Untitled

SELECT 
    split_part(SUM(total_time)::VARCHAR, ':', 1) as hours, 
    split_part(SUM(total_time)::VARCHAR, ':', 2) as minutes, 
    split_part(SUM(total_time)::VARCHAR, ':', 3) as seconds, 
    temp.wallet_address,
    users.fullname FROM (
    SELECT 
    (EXTRACT(EPOCH FROM (MAX(current_execute) - MIN(current_execute))::time) * interval '1 sec')::time as total_time
    , date_current_execute, wallet_address FROM (
        select MIN(current_execute) as current_execute, current_execute::date as date_current_execute, wallet_address from (
            select wallet_address, current_execute at time zone 'utc' at time zone 'ict' as current_execute from action_data
                WHERE action_id in (
                    select id from actions
                    WHERE tag = 'checkin'
                )
        ) as temp
        GROUP BY current_execute::date, wallet_address
        
        UNION ALL
        
        select MAX(current_execute) as current_execute, current_execute::date as date_current_execute, wallet_address from (
            select wallet_address, current_execute at time zone 'utc' at time zone 'ict' as current_execute from action_data
                WHERE action_id in (
                    select id from actions
                    WHERE tag = 'checkout'
                )
        ) as temp
        GROUP BY current_execute::date, wallet_address
    ) as temp
    GROUP BY date_current_execute, wallet_address
) as temp
JOIN users on users.wallet_address = temp.wallet_address
GROUP BY temp.wallet_address, users.fullname
ORDER BY split_part(SUM(total_time)::VARCHAR, ':', 1)::INTEGER DESC,
    split_part(SUM(total_time)::VARCHAR, ':', 2)::INTEGER DESC,
    split_part(SUM(total_time)::VARCHAR, ':', 3)::INTEGER DESC;