WITH cex_addresses AS (

SELECT DISTINCT address

FROM optimism.core.dim_labels

WHERE label_type = 'cex'

),

cex_flows_from AS (

SELECT block_timestamp::date as date, -SUM( raw_amount/1e18 ) as OP_from_cex

FROM optimism.core.fact_token_transfers

WHERE from_address IN (SELECT address FROM cex_addresses)

AND to_address NOT IN (SELECT address FROM cex_addresses)

AND contract_address = lower('0x4200000000000000000000000000000000000042') -- OP address

AND block_timestamp > '2022-10-01'

GROUP BY date

),

cex_flows_to AS (

SELECT block_timestamp::date as date, SUM( raw_amount/1e18 ) as OP_to_cex

FROM optimism.core.fact_token_transfers

WHERE to_address IN (SELECT address FROM cex_addresses)

AND from_address NOT IN (SELECT address FROM cex_addresses)

AND contract_address = lower('0x4200000000000000000000000000000000000042') -- OP address

AND block_timestamp > '2022-10-01'

GROUP BY date

),

cex_flows AS (

SELECT *, sum(OP_to_cex+OP_from_cex) OVER (ORDER BY date rows between unbounded preceding and current row) as cex_balance

FROM cex_flows_from FULL OUTER JOIN cex_flows_to USING (date)

),

OP_price_table as (

SELECT 'OP_price' as provider, hour as block_timestamp, symbol, price price_OP

FROM optimism.core.fact_hourly_token_prices

WHERE hour > '2022-10-01'

AND SYMBOL ='OP'

)