with trader_categorization as (

select

trader,

coalesce(sum(nullif(amount_in_usd, 0)), 0) as total_volume_usd,

ntile(100) over (order by coalesce(sum(nullif(amount_in_usd, 0)), 0) desc) as percentile

from near.defi.ez_dex_swaps

where block_timestamp >= '2024-01-01'

and block_timestamp < '2025-01-01'

and trader is not null

group by 1

having total_volume_usd > 0

),

whale_retail_metrics as (

select

date_trunc('week', d.block_timestamp) as week,

case

when t.percentile = 1 then 'whale'

else 'retail'

end as trader_type,

count(*) as num_swaps,

coalesce(sum(nullif(d.amount_in_usd, 0)), 0) as total_volume_usd,

case

when count(nullif(d.amount_in_usd, 0)) > 0

then coalesce(avg(nullif(d.amount_in_usd, 0)), 0)

else 0

end as avg_swap_size_usd,

count(distinct d.trader) as unique_traders,

case

when count(distinct d.trader) > 0

then coalesce(sum(nullif(d.amount_in_usd, 0)), 0) / count(distinct d.trader)

else 0

end as volume_per_trader

from near.defi.ez_dex_swaps d

inner join trader_categorization t on d.trader = t.trader