date_trunc('day', hour) as date,

token_address,

symbol,

decimals,

avg(price) as price

from optimism.core.fact_hourly_token_prices

group by 1,2,3,4

),txn_main4 as (select

tx_hash,

rank() over (partition by tx_hash order by event_index desc ) as rank

from optimism.core.fact_event_logs

where origin_to_address = lower('0xbE811A0D44E2553d25d11CB8DC0d3F0D0E6430E6')

and event_name = 'Swap'

qualify rank = 1

),txn4 as (select

b.block_timestamp,

b.origin_from_address,

b.tx_hash,

b.contract_address,

b.event_inputs,

rank() over (partition by b.tx_hash order by b.event_index) as rank

from txn_main4 a join optimism.core.fact_event_logs b on a.tx_hash = b.tx_hash

and b.event_name = 'Transfer'

order by b.tx_hash, b.event_index, rank

),swap4 as (select

a.block_timestamp,

a.origin_from_address,

a.tx_hash,

a.contract_address as from_token,

b.contract_address as to_token,

a.event_inputs:value as token_amount_in

from txn4 a join txn4 b on a.tx_hash = b.tx_hash and a.rank = 1 and b.rank = 2

),swap_txn4 as (select

block_timestamp,

tx_hash,