SELECT

INITCAP(LABEL_TYPE) AS SECTOR,

FROM_ADDRESS AS user_address,

COUNT(DISTINCT TX_HASH) AS tx_count

FROM base.core.fact_transactions a

LEFT JOIN base.core.dim_labels b ON a.to_address = b.address

WHERE LABEL_TYPE NOT IN ('chadmin')

AND b.project_name IS NOT NULL

AND a.status = 'SUCCESS'

GROUP BY LABEL_TYPE, FROM_ADDRESS

),

categorized_users AS (

SELECT

SECTOR,

CASE

WHEN tx_count = 1 THEN '1 Transaction (Non-Repeating)'

WHEN tx_count BETWEEN 2 AND 5 THEN '2 - 5 Transactions'

WHEN tx_count BETWEEN 6 AND 10 THEN '6 - 10 Transactions'

WHEN tx_count BETWEEN 11 AND 25 THEN '11 - 25 Transactions'

WHEN tx_count BETWEEN 26 AND 50 THEN '26 - 50 Transactions'

ELSE 'More Than 50 Transactions'

END AS transaction_category,

COUNT(user_address) AS user_count

FROM user_transaction_counts

GROUP BY SECTOR, transaction_category

SECTOR AS "Sector",

transaction_category AS "Transaction Category",

user_count AS "Number of Users"

FROM categorized_users

ORDER BY SECTOR, user_count DESC