select date_trunc('day', hour) as pdate, avg(price) as op_price

from optimism.core.fact_hourly_token_prices

where token_address = '0x4200000000000000000000000000000000000042'

group by pdate

),

swap_data as (

select date_trunc('day', block_timestamp) as date,

count(distinct tx_hash) as swaps,

count(distinct origin_from_address) as swappers,

sum(amount_in_usd) as usd_vol,

avg(amount_in_usd) as avg_usd_vol,

sum(usd_vol) over (order by date) as cumu_usd_vol,

sum(swaps) over (order by date) as cumu_swaps,

'Sushiswap' as platform

from optimism.sushi.ez_swaps

where date(block_timestamp) >= '2022-05-31'

group by date

UNION all

select date_trunc('day',a.block_timestamp) as date,

count(distinct a.tx_hash) as swaps,

count(distinct a.origin_from_address) as swappers,

sum((c.price * b.raw_amount)/pow(10, c.decimals)) as usd_vol,

avg((c.price * b.raw_amount)/pow(10, c.decimals)) as avg_usd_vol,

sum(usd_vol) over (order by date) as cumu_usd_vol,

sum(swaps) over (order by date) as cumu_swaps,

'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 date(a.block_timestamp) >= '2022-05-31'