with CEXes as (
select ADDRESS , LABEL PROJECT_NAME
from thorchain.core.dim_labels
where LABEL_TYPE = 'cex'
) ,
Transfer_from_CEXes_tx as (
select BLOCK_TIMESTAMP ::date date ,
FACT_TRANSFERS_ID tx_hash ,
TO_ADDRESS user,
(RUNE_AMOUNT_USD) amount ,
PROJECT_NAME CEX
from thorchain.core.fact_transfers join CEXes on FROM_ADDRESS = ADDRESS
where BLOCK_TIMESTAMP ::date >= '2024-01-01'
) ,
Transfer_to_CEXes_tx as (
select BLOCK_TIMESTAMP ::date date ,
FACT_TRANSFERS_ID tx_hash ,
FROM_ADDRESS user ,
(RUNE_AMOUNT_USD) amount ,
PROJECT_NAME CEX
from thorchain.core.fact_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 ,
sum (amount) volume
from all_
where flow = 'Inflow'