date_trunc('day', hour) as date,

symbol,

case when symbol = 'USDC' then '0xFF970A61A04b1cA14834A43f5dE4533eBDDB5CC8'

when symbol = 'DAI' then '0xDA10009cBd5D07dd0CeCc66161FC93D7c9000da1'

when symbol = 'USDT' then '0xFd086bC7CD5C481DCC9C85ebE478A1C0b69FCbb9'

when symbol = 'WBTC' then '0x2f2a2543B76A4166549F7aaB2e75Bef0aefC5B0f'

when symbol = 'WETH' then '0x82aF49447D8a07e3bd95BD0d56f35241523fBab1'

end as token_address,

decimals,

avg(price) as price

from ethereum.core.fact_hourly_token_prices

WHERE symbol in ('USDC','DAI','USDT','WETH','WBTC')

group by 1,2,3,4

),txn_main as (select

tx_hash,

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

from arbitrum.core.fact_event_logs

where origin_to_address = lower('0x0087BB802D9C0e343F00510000729031ce00bf27')

qualify rank = 1

),txn as (select

b.block_timestamp,

b.event_inputs:to as user,

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_main a join arbitrum.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

),swap as (select

a.block_timestamp,

b.user,

a.tx_hash,

a.contract_address as from_token,

b.contract_address as to_token,