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

select 'Inflow' flow , * from Transfer_to_CEXes_tx

select

flow ,

count (DISTINCT user) num_users ,

count (tx_hash) num_transactions ,

sum (amount) volume ,

volume/num_users average_per_user ,

volume / num_transactions average_per_transaction

from all_