-- Count the number of transactions per user

SELECT

sender,

COUNT(*) AS tx_count

FROM

aptos.core.fact_transactions

WHERE

tx_type = 'user_transaction'

AND success = TRUE

AND block_timestamp >= '2024-01-01'

GROUP BY

sender

SELECT

CASE

WHEN tx_count < 20 THEN '< 20 transactions'

WHEN tx_count BETWEEN 20 AND 50 THEN '20-50 transactions'

WHEN tx_count BETWEEN 51 AND 100 THEN '51-100 transactions'

WHEN tx_count BETWEEN 101 AND 500 THEN '101-500 transactions'

ELSE '> 500 transactions'

END AS tx_range,

COUNT(DISTINCT sender) AS user_count

FROM

user_tx_count

GROUP BY

tx_range

ORDER BY

tx_range;