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_main2 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('0xE592427A0AEce92De3Edee1F18E0157C05861564') or origin_to_address = lower('0x68b3465833fb72A70ecDF485E0e4C7bD8665Fc45')

and event_name = 'Swap'

qualify rank = 1

),txn2 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_main2 a join optimism.core.fact_event_logs b on a.tx_hash = b.tx_hash

and b.event_name = 'Transfer'

and b.origin_function_signature = '0x414bf389'

order by b.tx_hash, b.event_index, rank

),swap2 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 txn2 a join txn2 b on a.tx_hash = b.tx_hash and a.rank = 1 and b.rank = 2

),swap_txn2 as (select

block_timestamp,