SELECT DISTINCT address FROM monad.testnet.dim_contracts

),

user_activity AS (

SELECT

from_address AS user_address,

SUM(tx_fee) AS total_fees_paid

FROM monad.testnet.fact_transactions tx

LEFT JOIN contract_addresses c ON tx.from_address = c.address

WHERE tx.block_timestamp >= '2025-02-19'

AND c.address IS NULL -- Ensure it's an EOA

GROUP BY from_address

),

fee_percentiles AS (

-- Calculate fee percentiles to define categories

SELECT

APPROX_PERCENTILE(total_fees_paid, 0.5) AS median_fee, -- 50th percentile

APPROX_PERCENTILE(total_fees_paid, 0.95) AS top_5_fee -- 95th percentile (whale threshold)

FROM user_activity

SELECT

CASE

WHEN ua.total_fees_paid <= (SELECT median_fee FROM fee_percentiles) THEN 'Low Fee User'

WHEN ua.total_fees_paid > (SELECT top_5_fee FROM fee_percentiles) THEN 'Whale Fee User'

ELSE 'High Fee User'

END AS fee_category,

COUNT(*) AS user_count

FROM user_activity ua

CROSS JOIN fee_percentiles

GROUP BY fee_category

ORDER BY user_count DESC;