SELECT

tx_hash,

block_timestamp,

tx_signer AS user,

transaction_fee / 1e24 AS tx_fee

FROM

near.core.fact_transactions

WHERE

tx_succeeded

AND (

tx_receiver ILIKE ANY ('tg', '%herewallet.near', '%hot.tg')

OR tx:actions[0]:delegate:delegate_action:receiver_id ILIKE ANY ('tg', '%herewallet.near', '%hot.tg')

)

),

users AS (

SELECT

user,

COUNT(DISTINCT tx_hash) AS transactions,

MIN(block_timestamp) AS first_transaction_at

FROM main

GROUP BY 1

CASE

WHEN transactions = 1 THEN '🧍 One-time user (1 tx)'

WHEN transactions <= 5 THEN '🚶 Occasional user (2–5 txs)'

WHEN transactions <= 10 THEN '🏃 Moderate user (6–10 txs)'

WHEN transactions <= 25 THEN '🏋️ Active user (11–25 txs)'

WHEN transactions <= 50 THEN '🚴 Heavy user (26–50 txs)'

ELSE '🚀 Power user (51+ txs)'

END AS user_type,

COUNT(DISTINCT user) AS users

FROM users