select date_trunc('day',hour) as day, ifnull(avg(price),0) as price_of_OP

from optimism.core.fact_hourly_token_prices

where symbol ilike 'OP'

group by day),

sushi_table as (

select date_trunc ('day', block_timestamp) as day, case when

token_in ilike '0x4200000000000000000000000000000000000042' then 'swap_OP_to_others'

when token_out ilike '0x4200000000000000000000000000000000000042' then 'swap_others_to_OP'

else null end as type,

count (distinct origin_from_address) as number_of_swappers,

count (distinct tx_hash) as number_of_transactions,

sum (case when type = 'swap_OP_to_others' then amount_in

when type = 'swap_others_to_OP' then amount_Out end) as swap_amount

from optimism.sushi.ez_swaps where not type is null

group by 1,2)

select price.day, price_of_OP, type, sum(number_of_swappers) as swappers, sum(number_of_transactions) as num_tx, sum(swap_amount) as amount

from price, sushi_table where price.day = sushi_table.day

group by 1,2,3