uni_swap_tb as (

select

block_timestamp,

tx_hash,

0.0001 * 500 * 0.01 as fee, -- 0.05%

to_double(event_inputs:amount1 * pow(10, -6)) as bamount0,

to_double(event_inputs:amount0 * pow(10, -18)) as bamount1,

case when bamount1>0 then abs(bamount0) -- usdcOut exclude fees

else bamount0*(1-fee) end as amount_usd, -- usdcIn minus fees

case when bamount0>0 then abs(bamount1) -- ethOut exclude fees

else bamount1*(1-fee) end as eth_swapped,

case when bamount0>0 then bamount0*fee

else abs(bamount0)/(1-fee)*fee end as fees_collected_usd, -- tokenIn amount includes fees

abs(div0(amount_usd, eth_swapped)) as swapPrice,

case

when bamount0 > 0 then -1

when bamount0 < 0 then 1

end as direction

from

arbitrum.core.fact_event_logs

where

contract_address = '0xc31e54c7a869b9fcbecc14363cf510d1c41fa443'

and date(block_timestamp)>='2023-01-01'

and event_name = 'Swap'

and event_inputs:amount0 <> 0

and event_inputs:amount1 <> 0

and swapPrice<10000

order by

block_timestamp desc

),

fact_hourly as (

select hour as hour1, price

from crosschain.core.fact_hourly_prices

where token_address='0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2' AND