SELECT

DATE_TRUNC('day', block_timestamp) AS date,

CASE

WHEN amount_usd >= 50000 THEN 'Whale'

WHEN amount_usd BETWEEN 10000 AND 50000 THEN 'Investor'

ELSE 'Retail'

END AS user_category,

COUNT(DISTINCT from_address) AS unique_users

FROM avalanche.core.ez_token_transfers

WHERE to_address IN (SELECT DISTINCT pool_address FROM avalanche.defi.dim_dex_liquidity_pools)

AND block_timestamp >= DATEADD(MONTH, -6, CURRENT_DATE)

GROUP BY 1, 2

SELECT date, user_category, unique_users

FROM user_categories

ORDER BY date