-- forked from h4wk / user activity - Active Days @ https://flipsidecrypto.xyz/h4wk/q/a3Yo--DeMGTJ/user-activity---active-days with base as ( select signers[0] as signer , count(distinct tx_id) as txs , count(distinct block_timestamp::date) as days_active , txs / days_active , count(distinct program_id) as programs_used , min(block_timestamp::date) as first_tx_date , max(block_timestamp::date) as last_tx_date , case when days_active = 1 then '1.Active 1 Day' when days_active <= 5 then '2.Active < 5 Days' when days_active <= 7 then '3.Active < 7 Days' when days_active <= 10 then '4.Active < 10 Days' when days_active <= 15 then '5.Active < 15 Days' when days_active <= 20 then '6.Active < 20 Days' when days_active <= 30 then '7.Active < 30 Days' when days_active <= 50 then '8.Active < 50 Days' else 'Active > 50 Days' end as type , case when days_active = 1 then '1 Day' when days_active <= 5 then '< 5 Days' when days_active <= 7 then '< 7 Days' when days_active <= 10 then '< 10 Days' when days_active <= 15 then '< 15 Days' when days_active <= 20 then '< 20 Days' when days_active <= 30 then '< 30 Days' when days_active <= 50 then '< 50 Days' else '> 50 Days' end as type_2 from eclipse.core.fact_events where 1 = 1 -- and block_timestamp > current_date - 7 and program_id not in ( 'BPFLoaderUpgradeab1e11111111111111111111111' , 'ComputeBudget111111111111111111111111111111' , '11111111111111111111111111111111' , 'TokenzQdBNbLqP5VEhdkAS6EPFLC1PHnBqCXEpPxuEb' , 'ATokenGPvbdGVxr1b2hvZbsiqW5xWH25efTNsLJA8knL' , 'TokenkegQfeZyiNwAJbNbGKPFXCWuBvf9Ss623VQ5DA' , 'FUCHhfHbuxXBWiRBfVdhmiog84sUJw11aAq3ibAUGL6e' -- a lot of txs but only 2 signers , '5hEa5j38yNJRM9vQA44Q6gXVj4Db8y3mWxkDtQeofKKs' -- very probable a airdrop botting farmer ) and signers[0] != 'G5FM3UKwcBJ47PwLWLLY1RQpqNtTMgnqnd6nZGcJqaBp' -- hyperlane signer group by 1 -- qualify row_number() over (order by txs desc) <= 10 -- or row_number() over (order by programs_used desc) <= 10 -- or row_number() over (order by days_active desc) <= 10 -- order by txs desc limit 100 ) select type, type_2, count(signer) as user_count, sum(user_count) over (order by type) as cumu_users from base group by 1, 2 order by type

0x9b5Cb153d1cE74beB84F407c7f93e8c6EBD1d6Ae