SELECT *

FROM solana.core.dim_labels

WHERE label_type ilike '%cex%'

),

Inflow AS (

SELECT date_trunc('day',block_timestamp) as date,

sum (amount) AS Volume_In

FROM solana.core.fact_transfers

WHERE tx_to in (select distinct address from CEX)

AND tx_from not in (select distinct address from CEX)

and mint = 'So11111111111111111111111111111111111111112'

and block_timestamp >= '2022-11-02'

group by 1),

Outflow as (

select date_trunc('day',block_timestamp) as date,

sum (amount) as Volume_Out

from solana.core.fact_transfers

where tx_from in (select distinct address from CEX)

and tx_to not in (select distinct address from CEX)

and mint = 'So11111111111111111111111111111111111111112'

and block_timestamp >= '2022-11-02'

group by 1)

select t1.date,

Volume_IN,

Volume_Out*-1 as Volume_OUT,

Volume_IN - Volume_Out as Net_Volume

from Inflow t1 join Outflow t2 on t1.date = t2.date