SELECT

from_address AS user,

block_timestamp::DATE AS transaction_date

FROM

swell.core.fact_transactions

WHERE

tx_succeeded

),

user_active_days AS (

SELECT

user,

COUNT(DISTINCT transaction_date) AS active_days

FROM

main

GROUP BY

user

CASE

WHEN active_days >= 100 THEN '🔥 Super Active (100+ days)'

WHEN active_days BETWEEN 50 AND 99 THEN '⚡ Very Active (50-99 days)'

WHEN active_days BETWEEN 20 AND 49 THEN '🚀 Active (20-49 days)'

WHEN active_days BETWEEN 5 AND 19 THEN '🐣 Occasional (5-19 days)'

WHEN active_days BETWEEN 1 AND 4 THEN '🍼 Rare (1-4 days)'

ELSE '❓ Unknown'

END AS activity_category,

COUNT(*) AS user_count

user_active_days

GROUP BY

activity_category

ORDER BY

user_count DESC