SELECT

from_address AS new_users,

MIN(block_timestamp::DATE) AS first_tx_date

FROM kaia.core.fact_transactions

WHERE tx_succeeded = 'TRUE'

GROUP BY 1

, user_transaction_counts AS (

SELECT

n.new_users,

COUNT(a.tx_hash) AS total_txns

FROM kaia.core.fact_transactions a

JOIN first_time_users n ON a.from_address = n.new_users

WHERE a.tx_succeeded = 'TRUE'

AND n.first_tx_date >= '2024-09-25'

GROUP BY 1

, categorized_users AS (

SELECT

CASE

WHEN total_txns = 1 THEN '01. One-time user (1 tx)'

WHEN total_txns = 2 THEN '02. Tried twice (2 tx)'

WHEN total_txns BETWEEN 3 AND 10 THEN '03. Occasional (3-10 tx)'

WHEN total_txns BETWEEN 11 AND 20 THEN '04. Regular (11-20 tx)'

WHEN total_txns BETWEEN 21 AND 50 THEN '05. Active (21-50 tx)'

WHEN total_txns BETWEEN 51 AND 100 THEN '06. Very Active (51-100 tx)'

WHEN total_txns BETWEEN 101 AND 500 THEN '07. Power User (101-500 tx)'

WHEN total_txns BETWEEN 501 AND 1000 THEN '08. Super User (501-1000 tx)'

ELSE '09. Elite (1000+ tx)'

END AS user_category,

COUNT(DISTINCT new_users) AS user_count

FROM user_transaction_counts

GROUP BY 1