SELECT

FROM_ADDRESS,

COUNT(DISTINCT TX_HASH) AS unique_txs

FROM boba.core.fact_transactions

WHERE BLOCK_TIMESTAMP >= DATEADD('year', -1, CURRENT_DATE)

GROUP BY FROM_ADDRESS

),

user_segments AS (

SELECT

CASE

WHEN unique_txs = 1 THEN 'New (1 tx)'

WHEN unique_txs BETWEEN 2 AND 10 THEN 'Rare (2-10 txs)'

WHEN unique_txs BETWEEN 11 AND 50 THEN 'Casual (11-50 txs)'

WHEN unique_txs BETWEEN 51 AND 500 THEN 'Active (51-500 txs)'

WHEN unique_txs BETWEEN 501 AND 5000 THEN 'Heavy (501-5000 txs)'

ELSE 'Super (>5000 txs)'

END AS user_segment,

COUNT(*) AS user_count

FROM user_tx_counts

GROUP BY 1

SELECT

user_segment,

user_count,

ROUND(user_count * 100.0 / SUM(user_count) OVER(), 2) AS percentage

FROM user_segments

ORDER BY user_count DESC;