SELECT

ACCOUNT,

COUNT(DISTINCT TRANSACTION_HASH) AS txn_count

FROM stellar.core.fact_transactions

WHERE BLOCK_TIMESTAMP >= DATEADD(YEAR, -1, CURRENT_DATE)

GROUP BY ACCOUNT

),

user_distribution AS (

SELECT

CASE

WHEN txn_count = 1 THEN '1 Txn'

WHEN txn_count BETWEEN 2 AND 5 THEN '2-5 Txns'

WHEN txn_count BETWEEN 6 AND 20 THEN '6-20 Txns'

WHEN txn_count BETWEEN 21 AND 50 THEN '21-50 Txns'

ELSE '50+ Txns'

END AS txn_category,

COUNT(*) AS user_count

FROM user_txn_counts

GROUP BY txn_category

SELECT *

FROM user_distribution

ORDER BY user_count DESC;