with

t1 as (

select

date(swaps.block_timestamp) as date,

case

when swaps.swap_program ilike '%raydium%' then 'Raydium'

when swaps.swap_program ilike '%jupiter%' then 'Jupiter'

when swaps.swap_program ilike '%phoenix%' then 'Phoenix'

when swaps.swap_program ilike '%meteora%' then 'Meteora'

when swaps.swap_program ilike '%orca%' then 'Orca'

when swaps.swap_program ilike '%bonk%' then 'Bonkswap'

when swaps.swap_program ilike '%saber%' then 'Saber'

when swaps.swap_program ilike '%stepn%' then 'Stepn Swap'

else swaps.swap_program

end as dex,

swaps.swap_to_amount as swap_amount_token,

swaps.swap_to_mint,

(swap_amount_token * prices.price) as swap_amount_usd

from solana.defi.fact_swaps swaps

inner join solana.price.ez_prices_hourly prices

on date_trunc('hour', swaps.block_timestamp) = prices.hour

and (swaps.swap_to_mint = prices.token_address

or (case when swaps.swap_to_mint = 'So11111111111111111111111111111111111111111'

then TRUE else FALSE end = prices.is_native

and prices.token_address is null

)

)

where swaps.block_timestamp >= current_date - 8

and swaps.block_timestamp < current_date

and succeeded

)