trunc(block_timestamp,'day') as day,

tx_hash,

origin_from_address,

concat(symbol_in,'/',symbol_out) as pair,

token_in,

token_out,

amount_in,

amount_out,

amount_in_usd,

from polygon.defi.ez_dex_swaps

where block_timestamp::date >= current_date - 90

and platform ilike '%quickswap%')

,

tb2 as ( select trunc(hour,'day') as day,

token_address,

avg(price) as price

from bsc.price.ez_hourly_token_prices

group by 1,2)

,

tb3 as ( select chain,

a.day,

tx_hash,

origin_from_address,

pair,

case when amount_in_usd is null then amount_in*b.price else amount_in_usd end as usd_in,

case when amount_out_usd is null then amount_out*c.price else amount_out_usd end as usd_out,

case when usd_in is null then usd_out else usd_in end as volume

from tb1 a left outer join tb2 b on a.day = b.day and a.token_in = b.token_address

left outer join tb2 c on a.day = c.day and a.token_out = c.token_address

select

case