SELECT

sender AS wallet,

COUNT(DISTINCT tx_hash) AS total_transactions

FROM

aptos.core.fact_transactions

WHERE

success = TRUE

AND wallet != ''

GROUP BY

sender

)

SELECT

category.transaction_category,

COUNT(*) AS count_of_wallets,

COALESCE(SUM(tc.total_transactions), 0) AS count_of_transactions

FROM

(

SELECT

CASE

WHEN total_transactions < 20 THEN 'less than 20 transactions'

WHEN total_transactions >= 20 AND total_transactions < 200 THEN '20-200 transactions'

WHEN total_transactions >= 200 AND total_transactions < 1000 THEN '200-1000 transactions'

ELSE 'over 1000 transactions'

END AS transaction_category,

COUNT(*) AS count_of_wallets

FROM

TransactionCounts

GROUP BY

transaction_category

) category

LEFT JOIN TransactionCounts tc ON category.transaction_category = CASE

WHEN tc.total_transactions < 20 THEN 'Less than 20 transactions'

WHEN tc.total_transactions >= 20

AND tc.total_transactions < 200 THEN '20-200 transactions'

WHEN tc.total_transactions >= 200