price_table as (

select

date_trunc('day', RECORDED_HOUR ) as days, SYMBOL,

TOKEN_ADDRESS,

avg(close) as price

from solana.core.ez_token_prices_hourly

where symbol not in ('cow','boo','tiny','cash')

group by 1,2,3

)

,

swap_table as (

select s.*,

SWAP_FROM_AMOUNT*p.price as SWAP_FROM_AMOUNT_usd,p.SYMBOL as token_from,

SWAP_to_AMOUNT*t.price as SWAP_to_AMOUNT_usd,t.SYMBOL as token_to,

concat(token_from,'-->',token_to) as swap_pair

from solana.core.fact_swaps s

join price_table p on s.BLOCK_TIMESTAMP::date = p.days and s.SWAP_FROM_MINT = p.token_address

join price_table t on s.BLOCK_TIMESTAMP::date = t.days and s.SWAP_to_mint = t.token_address

where BLOCK_TIMESTAMP::date>= CURRENT_DATE-{{days_back}}

and token_from <> token_to

)

select swap_pair,count(distinct tx_id) as trades,

sum(SWAP_FROM_AMOUNT_usd) tot_SWAP_FROM_AMOUNT_usd,

sum(SWAP_to_AMOUNT_usd) tot_SWAP_to_AMOUNT_usd,