with price_table as

select date_trunc('hour',block_timestamp) as hour, '0xeb6d78148f001f3aa2f588997c5e102e489ad341' as token_address, avg(amount_out/amount_in) as price from base.defi.ez_dex_swaps

where token_in='0xeb6d78148f001f3aa2f588997c5e102e489ad341'

and token_out='0x833589fcd6edb6e08f4c7c32d4f71b54bda02913'

and contract_address='0x58c5649e6bc93969f4530673e8892faf762cc7e7'

AND block_timestamp>= '2024-05-06'

group by hour

union all

select hour, token_address, price from base.price.ez_prices_hourly

where hour>= '2024-05-06'

select es.*, pt_in.price as price_in, pt_out.price as price_out,

case when token_out='0xeb6d78148f001f3aa2f588997c5e102e489ad341' then es.amount_out*pt_out.price

else 1*es.amount_out

end as amount_out_usd_true,

case when token_in='0xeb6d78148f001f3aa2f588997c5e102e489ad341' then es.amount_in*pt_in.price

else 1*es.amount_in

end as amount_in_usd_true

from base.defi.ez_dex_swaps es

left join price_table pt_in

on (es.token_in=pt_in.token_address and date_trunc('hour',block_timestamp)=pt_in.hour)

left join price_table pt_out

on (es.token_out=pt_out.token_address and date_trunc('hour',block_timestamp)=pt_out.hour)

where 1=1

and es.contract_address='0x58c5649e6bc93969f4530673e8892faf762cc7e7'

and es.symbol_out='USDC'