select

block_timestamp,

tx_hash,

origin_from_address

from optimism.core.fact_event_logs

where tx_status = 'SUCCESS'

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

),

transactions_results as (

select

date_trunc('day', block_timestamp) as date,

count(distinct tx_hash) as "Number of Transactions",

count(distinct origin_from_address) as "Daily Active User"

from optimism_transactions

group by 1

),

op_token_price as (

select

hour::date as date,

avg(price) as "OP Price"

from optimism.core.fact_hourly_token_prices

where hour::date >= '2022-11-01'

and token_address = '0x4200000000000000000000000000000000000042'

group by 1

select

transactions.*, case

when date between '2022-11-06' and '2022-11-09' then 'Op price drop' else 'Other days' end as type,

op_price."OP Price" as "OP Price"

from transactions_results transactions

join op_token_price op_price using(date)