Get avg time check-in

mail@pastecode.io avatar
unknown
pgsql
2 years ago
2.1 kB
1
Indexable
Never
SELECT 
'2022-10-24'::date + (SUM(temp1.min_current_execute::time) / COUNT(temp1.wallet_address))::interval as aaa,
users.wallet_address,
users.username,
users.fullname,
users.token_id,
levels.level_name as level,
users.lp,
users.rp,
COALESCE(metadata.image, '') AS image
from 
    (with data as (
    with vars as (
        select (date_trunc('week', '2022-10-24 22:24:22'::timestamp) + '1 seconds'::interval)::timestamp at time zone 'ict' at time zone 'utc' as datestart,
                (date_trunc('week', '2022-10-24 22:24:22'::timestamp)+ '7 days'::interval - '1 seconds'::interval)::timestamp at time zone 'ict' at time zone 'utc' as dateend
        )
        select action_id, wallet_address, current_execute at time zone 'utc' at time zone 'ict' current_execute from action_data, vars
        WHERE action_data.current_execute >= vars.datestart and action_data.current_execute <= vars.dateend
        AND action_data.action_id in (select id from actions WHERE tag in ('checkin'))
    )
    SELECT MIN(data.current_execute) as min_current_execute, data.current_execute::date as time_current_execute, data.wallet_address from data, actions
    where tag = 'checkin' and data.action_id = actions.id
    GROUP BY data.current_execute::date, data.wallet_address
    ORDER BY min_current_execute) AS temp1
    JOIN users ON users.wallet_address = temp1.wallet_address
    LEFT JOIN metadata ON metadata.wallet_address = users.wallet_address AND users.level = metadata.level
    LEFT JOIN levels ON users.level = levels.level
GROUP BY users.wallet_address, users.username, users.fullname, users.token_id, levels.level_name, users.lp, users.rp, metadata.image
HAVING users.wallet_address NOT IN ('0x48Cc8048473Dc3deeF511fb0735f4eF402a7Cf4D',
'0x99A4d54bDaE2362DE182E3029c67A89CedDa292b',
'0xc5D0aF104D201D4631c7269a1Bf3b0D56f740767',
'0x3640FDB6Ff891533429aa0554e809fB45EB4f0E0',
'0xA7841297eC48e262db0F96992d55a28967AF287e',
'0x6613106eAcd50451D0685a56942d5AebF93A782E',
'0xC47dCa519431B23b0548B00f89E5900f9Fc7d0eE',
'0x56A1ffb93467bf4BCce290276b3B8c765180D332',
'0x0f4bc0D03dC99da6ef064D6803A23bE6984c7981'
)
order by aaa