with CEXes as (
select ADDRESS , PROJECT_NAME
from kaia.core.dim_labels
where LABEL_TYPE = 'cex'
) ,
Transfer_from_CEXes_tx as (
select BLOCK_TIMESTAMP ::date date ,
tx_hash ,
TO_ADDRESS user,
AMOUNT ,
AMOUNT_USD,
PROJECT_NAME CEX
from kaia.core.ez_native_transfers join CEXes on FROM_ADDRESS = ADDRESS
where BLOCK_TIMESTAMP ::date >= '2024-01-01'
)
,
Transfer_to_CEXes_tx as (
select BLOCK_TIMESTAMP ::date date ,
tx_hash ,
FROM_ADDRESS user ,
amount ,
AMOUNT_USD,
PROJECT_NAME CEX
from kaia.core.ez_native_transfers join CEXes on TO_ADDRESS = ADDRESS
where BLOCK_TIMESTAMP ::date >= '2024-01-01'
),
all_ as (
select 'Outflow' flow , * from Transfer_from_CEXes_tx
UNION
select 'Inflow' flow , * from Transfer_to_CEXes_tx
)
select
CEX ,
count (DISTINCT user) num_users ,
count (tx_hash) num_transactions ,