table_1 as ( select distinct tx_hash as tx_hash FROM optimism.core.fact_event_logs

where event_name = 'Swap'

)

,

o_swap_from as (

SELECT block_timestamp, tx_hash, ORIGIN_FROM_ADDRESS, symbol as swap_from_symbol, RAW_AMOUNT / POW(10, decimals) as swap_from_amount

FROM optimism.core.fact_token_transfers t LEFT JOIN optimism.core.dim_contracts c ON t.contract_address = c.address

WHERE tx_hash in (select tx_hash from table_1)

),

o_swap_to as (

SELECT block_timestamp, tx_hash, ORIGIN_FROM_ADDRESS, symbol as swap_to_symbol, RAW_AMOUNT / POW(10, decimals) as swap_to_amount

FROM optimism.core.fact_token_transfers t LEFT JOIN optimism.core.dim_contracts c ON t.contract_address = c.address

WHERE tx_hash in (select tx_hash from table_1)

, o_combined as (

SELECT f.block_timestamp, f.tx_hash, f.ORIGIN_FROM_ADDRESS, swap_from_symbol, swap_from_amount, swap_to_symbol, swap_to_amount,

IFF(LEFT(swap_from_symbol, 1) < LEFT(swap_to_symbol, 1), CONCAT(swap_from_symbol, '-', swap_to_symbol), CONCAT(swap_to_symbol, '-', swap_from_symbol)) as asset_pair

FROM o_swap_from f INNER JOIN o_swap_to t ON f.tx_hash = t.tx_hash

select date_trunc('day',block_timestamp) as hourly_date, swap_to_symbol, sum(swap_to_amount), sum(swap_to_amount*b.price) as swap_to_amount_usdc, sum(swap_to_amount*c.price) as swap_to_amount_usdc_2,

case when swap_to_amount_usdc is null then swap_to_amount_usdc_2

else swap_to_amount_usdc

end as swap_amount_combined from o_combined a

left join ethereum.core.fact_hourly_token_prices b

on upper(a.swap_to_symbol) = upper(b.symbol) and date_trunc('hour',block_timestamp) = b.hour

left join optimism.core.fact_hourly_token_prices c