WITH UserTransactionCounts AS (

SELECT

tx_from as user,

min(date_trunc('day', block_timestamp)) as day,

count(DISTINCT tx_id) as transactions,

count(DISTINCT date_trunc('day', block_timestamp)) as active_days

FROM sei.core.fact_transactions

WHERE block_timestamp > '2023-05-30'

GROUP by 1

SELECT

CASE

WHEN transactions = 1 THEN 'a 1 tx'

WHEN transactions BETWEEN 2 AND 5 THEN 'b 2 - 5 tx'

WHEN transactions BETWEEN 6 AND 10 THEN 'c 6 - 10 tx'

WHEN transactions BETWEEN 11 AND 50 THEN 'd 11 - 50 tx'

WHEN transactions BETWEEN 51 AND 500 THEN 'e 51 - 500 tx'

ELSE 'f 500+ tx'

END AS transaction_count_group,

COUNT(user) AS number_of_users

FROM UserTransactionCounts

GROUP BY transaction_count_group

ORDER BY

CASE

WHEN transaction_count_group = '1 tx' THEN 1

WHEN transaction_count_group = '2 - 5 tx' THEN 2

WHEN transaction_count_group = '6 - 10 tx' THEN 3

WHEN transaction_count_group = '11 - 50 tx' THEN 4

WHEN transaction_count_group = '51 - 500 tx' THEN 5

ELSE 6

END;