WITH

active_receivers as (

SELECT

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

tx_signer as from_address,

count(distinct tx_receiver) as n_users,

count(distinct x.tx_hash) as txs,

sum(deposit/pow(10,24)) as volume

from near.core.fact_transfers x

where tx_signer in (select distinct address from near.core.dim_address_labels where label_type='cex')

group by 1,2

),

active_senders as (

SELECT

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

tx_receiver as to_address,

count(distinct tx_signer) as n_users,

count(distinct x.tx_hash) as txs,

sum(deposit/pow(10,24))as volume

from near.core.fact_transfers x

where tx_receiver in (select distinct address from near.core.dim_address_labels where label_type='cex')

group by 1,2

),

active_users as (

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

ifnull(x.to_address,y.from_address) as cex,

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,

sendings_to_cex-receivings_from_cex as netflow_sendings_to_cex,

ifnull(x.volume,0) as volume_to_cex,

ifnull(y.volume,0) as volume_from_cex,