select hour::date as day,

avg (price) as OP_Price

from optimism.core.fact_hourly_token_prices

where symbol = 'OP'

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

group by 1),

cext as (select distinct address from optimism.core.dim_labels where label_type = 'cex'),

maintable as (

select block_timestamp::date as date,

OP_Price,

case when to_address in (select address from cext) and from_address not in (select address from cext) then '$OP Inflow To CEXs'

when from_address in (select address from cext) and to_address not in (select address from cext) then '$OP Outflow From CEXs'

else null end as transfer_type,

count (distinct tx_hash) as TX_Count,

sum (raw_amount/1e18) as Total_Volume,

avg (raw_amount/1e18) as Average_Volume

from optimism.core.fact_token_transfers t1 join oppricet t2 on t1.block_timestamp::date = t2.day

where block_timestamp::Date >= '2022-11-01'

and contract_address = '0x4200000000000000000000000000000000000042'

and transfer_type is not null

group by 1,2,3)

select case when date >= '2022-11-08' and date < '2022-11-15' then 'FTX Collapse Week'

when date >= '2022-11-28' then 'Recent Weeks Bullish Run'

else 'Other Days' end as timespan,

transfer_type,

avg (tx_count) as Average_TX_Count,

avg (total_volume) as Average_Volume

from maintable

where date != CURRENT_DATE

group by 1,2