SELECT

swapper,

datediff(day,'2023-08-15',current_date) as days,

count(DISTINCT tx_id) 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 sei.defi.fact_dex_swaps

WHERE block_timestamp >= '2023-08-15'

GROUP BY 1

groups,