t1 as (

SELECT trunc(hour,'{{granularity}}') AS date,

avg(price) as op_price

from optimism.core.fact_hourly_token_prices where symbol = 'OP'

GROUP BY 1

),

t2 as (

SELECT trunc(hour,'{{granularity}}') AS date,

avg(price) as eth_price

from optimism.core.fact_hourly_token_prices where symbol = 'ETH'

GROUP BY 1

) ,

t3 as (

select trunc(block_timestamp,'{{granularity}}') as date,

count(distinct origin_from_address) as swappers_from,

count(distinct tx_hash) as swaps_from,

sum(EVENT_INPUTS:amountIn/1e18) as volume_from

from optimism.core.fact_event_logs

WHERE event_name = 'Swap' and EVENT_INPUTS:tokenIn='0x4200000000000000000000000000000000000042' and tx_status = 'SUCCESS'

GROUP BY 1

),

t4 as (

select trunc(block_timestamp,'{{granularity}}') as date,

count(distinct origin_from_address) as swappers_to,

count(distinct tx_hash) as swaps_to,

sum(EVENT_INPUTS:amountOut/1e18) as volume_to

from optimism.core.fact_event_logs

WHERE event_name = 'Swap' and EVENT_INPUTS:tokenOut='0x4200000000000000000000000000000000000042' and tx_status = 'SUCCESS'

GROUP BY 1

)

t1.date,

op_price,

eth_price,

swappers_from as swappers_selling_op,swappers_to as swappers_buying_op,