with activities as ( SELECT from_address as user, datediff(day,'2023-09-20',current_date) as days, count(DISTINCT tx_hash) as n_txs, CASE when n_txs = 1 then '1 transaction' when n_txs >1 AND n_txs <= days*1/5 then '<=' || floor(days*1/5) || 'txns' when n_txs >days*1/5 AND n_txs <= days*1/2 then '<=' || floor(days*1/2) || ' txns' when n_txs > days*1/2 AND n_txs <= days*1 then '<=' || floor(days*1) || ' txns' when n_txs > days*1 AND n_txs <= days*2 then '<=' || floor(days*2) || ' txns' when n_txs > days*2 AND n_txs <= days*5 then '<=' || floor(days*5) || ' txns' when n_txs > days*5 AND n_txs <= days*50 then '<=' || floor(days*50) || ' txns' else '>' || floor(days*50) || ' txns' end as type, CASE when n_txs = 1 then 'one txn' when n_txs >1 AND n_txs <= days*1/5 then 'every 5 days 1 txn' when n_txs >days*1/5 AND n_txs <= days*1/2 then 'every 2 days 1 txn' when n_txs > days*1/2 AND n_txs <= days*1 then 'one txn per day' when n_txs > days*1 AND n_txs <= days*2 then '>2 txns per day' when n_txs > days*2 AND n_txs <= days*5 then '>5 txns per day' when n_txs > days*5 AND n_txs <= days*50 then '>50 txns per day' else '>50 txns per day' end as tx_interval, CASE when tx_interval = 'one txn' then 'g1' when tx_interval = 'every 5 days 1 txn' then 'g2' when tx_interval = 'every 2 days 1 txn' then 'g3' when tx_interval = 'one txn per day' then 'g4' when tx_interval = '>2 txns per day' then 'g5' when tx_interval = '>5 txns per day' then 'g6' when tx_interval = '>50 txns per day' then 'g7' else 'g8' end as groups FROM avalanche.core.fact_transactions WHERE to_address = '0xa481b139a1a654ca19d2074f174f17d7534e8cec' AND block_timestamp::date >= '2023-09-20' GROUP BY 1 ) SELECT groups, type, tx_interval, count(user) as count_user FROM activities GROUP BY 1,2,3 ORDER BY 1

StarsArena Analytica