select

address

from optimism.core.dim_labels

where label_type = 'cex'

),

cex_transfer as (

select

*

from optimism.core.fact_token_transfers

where to_address in (select address from cex_address)

and from_address not in (select address from cex_address)

and block_timestamp::date >= '2022-07-01' and block_timestamp::date <= '2022-07-31'

and raw_amount > 0 and raw_amount is not null

and contract_address = '0x4200000000000000000000000000000000000042'

),

op_token_price as (

select

hour::date as "Days",

avg(price) as "OP Price"

from optimism.core.fact_hourly_token_prices

where hour::date >= '2022-07-01' and hour::date <= '2022-07-31'

and token_address = '0x4200000000000000000000000000000000000042'

group by "Days"

),

get_cex_transfer as (

select

block_timestamp,

tx_hash,

origin_from_address,

(raw_amount / 1e18) as op_amount

from cex_transfer

),

final_cex_transfer as (

select

date_trunc('day', block_timestamp) as "Days",