WITH

active_receivers as (

SELECT

trunc(x.block_timestamp,'week') as date,

--to_address,

symbol,

count(distinct origin_from_address) as n_users,

count(distinct x.tx_hash) as txs,

sum(amount_usd) as volume

from avalanche.core.ez_token_transfers x

where to_address in (select distinct address from avalanche.core.dim_labels where label_type='cex')

group by 1,2

),

active_senders as (

SELECT

trunc(x.block_timestamp,'week') as date,

--from_address,

symbol,

count(distinct origin_from_address) as n_users,

count(distinct x.tx_hash) as txs,

sum(amount_usd)as volume

from avalanche.core.ez_token_transfers x

where from_address in (select distinct address from avalanche.core.dim_labels where label_type='cex')

group by 1,2

),

active_users as (

ifnull(x.date,y.date) as date,

ifnull(x.symbol,y.symbol) as symbol,

ifnull(x.n_users,0) as senders_to_cex,

ifnull(y.n_users,0) as receivers_from_cex,

senders_to_cex-receivers_from_cex as netflow_users_to_cex,

ifnull(x.txs,0) as sendings_to_cex,

ifnull(y.txs,0) as receivings_from_cex,