select

tx_id,

block_timestamp,

swapper,

swap_from_symbol as symbol_in,

swap_to_symbol as symbol_out,

abs(nvl(swap_from_amount_usd, swap_to_amount_usd)) as amount_usd,

case

when platform ilike 'jupiter%' then 'Jupiter'

when platform ilike 'raydium%' then 'Raydium'

when platform ilike 'meteora%' then 'Meteora'

when platform ilike 'saber%' then 'Saber'

when platform ilike 'orca%' then 'Orca'

else initcap(platform)

end as platform

from solana.marinade.ez_swaps

where succeeded

and 'MNDE' in (symbol_in, symbol_out)

),

first_swaps as (

select swapper, min(block_timestamp) as first_swap_date

from main

group by swapper

),

daily_metrics as (

select

date_trunc('{{ period }}', m.block_timestamp) as day,

count(*) as swaps, -- Total number of swaps

count(distinct m.swapper) as active_swappers, -- Unique swappers per day

count(distinct case when f.first_swap_date = m.block_timestamp then m.swapper end) as new_swappers, -- First-time swappers

count(distinct case when f.first_swap_date < m.block_timestamp then m.swapper end) as old_swappers, -- Returning swappers

sum(m.amount_usd) as total_volume_usd, -- Daily swap volume in USD

avg(m.amount_usd) as avg_swap_size_usd -- Average swap size

from main m

left join first_swaps f on m.swapper = f.swapper