with price_table as (select date_trunc('day', hour) as dt, 'OP' as symbol, avg(price) as OP_price

from optimism.core.fact_hourly_token_prices

where symbol in ('OP')

group by 1, 2),

sushiswap_tab as (select block_timestamp, tx_hash, amount_in_usd, amount_out_usd * -1 as amount_out_usd, 'Sushiswap' as platform

from optimism.sushi.ez_swaps

where block_timestamp::date >= '2022-11-01'),

velodrome_tab as (select block_timestamp, tx_hash, amount_in_usd, amount_out_usd * -1 as amount_out_usd, 'Velodrome' as platform

from optimism.velodrome.ez_swaps

where block_timestamp::date >= '2022-11-01'),

uniswap_in_tab as (select a.block_timestamp, a.tx_hash, (c.price * b.raw_amount)/pow(10, c.decimals) as amount_in_usd, 'Uniswap' as platform

from optimism.core.fact_event_logs a

join optimism.core.fact_token_transfers b on a.tx_hash = b.tx_hash

join optimism.core.fact_hourly_token_prices c on c.token_address = b.contract_address and date_trunc('hour', b.block_timestamp) = c.hour

where a.event_name = 'Swap'

and a.origin_to_address in ('0x68b3465833fb72a70ecdf485e0e4c7bd8665fc45','0xe592427a0aece92de3edee1f18e0157c05861564')

and a.tx_status ='SUCCESS'

and a.block_timestamp::date >= '2022-11-01'

),

uniswap_out_tab as (

select a.block_timestamp, a.tx_hash, (c.price * b.raw_amount)/pow(10, c.decimals) * -1 as amount_out_usd, 'Uniswap' as platform

from optimism.core.fact_event_logs a

join optimism.core.fact_token_transfers b on a.tx_hash = b.tx_hash

join optimism.core.fact_hourly_token_prices c on c.token_address = b.contract_address and date_trunc('hour', b.block_timestamp) = c.hour

where a.event_name = 'Swap'

and a.origin_from_address in ('0x68b3465833fb72a70ecdf485e0e4c7bd8665fc45','0xe592427a0aece92de3edee1f18e0157c05861564')

and a.tx_status ='SUCCESS'

and a.block_timestamp::date >= '2022-11-01'

),

daily_Sushi as