SELECT

origin_from_address,

date_trunc('day',block_timestamp) as date

FROM avalanche.core.fact_event_logs

WHERE origin_to_address = lower('0xa695ea0C90D89a1463A53Fa7a02168Bc46FbBF7e')

GROUP BY 1, 2

),

active_wallets as (

SELECT

origin_from_address,

datediff(day,'2022-05-16',current_date) as days,

count(date) as n_d,

CASE when n_d = 1 then '1 day'

when n_d >1 AND n_d <=days*1/100 then 'active <= 1%'

when n_d >days*1/100 AND n_d <=days*5/100 then 'active <= 5%'

when n_d >days*5/100 AND n_d <=days*10/100 then 'active <= 10%'

when n_d >days*10/100 AND n_d <=days*25/100 then 'active <= 25%'

when n_d >days*25/100 AND n_d <=days*50/100 then 'active <= 50%'

else 'active day > 50%' end as type,

CASE when n_d = 1 then '1 day'

when n_d >1 AND n_d <=days*1/100 then 'active <=' || floor(days*1/100) || ' days'

when n_d >days*1/100 AND n_d <=days*5/100 then 'active <=' || floor(days*5/100) || ' days'

when n_d >days*5/100 AND n_d <=days*10/100 then 'active <=' || floor(days*10/100) || ' days'

when n_d >days*10/100 AND n_d <=days*25/100 then 'active <=' || floor(days*25/100) || ' days'

when n_d >days*25/100 AND n_d <=days*50/100 then 'active <=' || floor(days*50/100) || ' days'

else 'active >' || floor(days*50/100) || ' days' end as days_active,

CASE when type = '1 day' then 'G1'

when type = 'active <= 1%' then 'G2'

when type = 'active <= 5%' then 'G3'

when type = 'active <= 10%' then 'G4'

when type = 'active <= 25%' then 'G5'

when type = 'active <= 50%' then 'G6'

else 'G8' end as groups

FROM activities