select

address,

Project_name as CEX_Name

from avalanche.core.dim_labels

where label_type = 'cex'

),

ez_transfer as (

select

BLOCK_TIMESTAMP,

TX_HASH,

ORIGIN_FROM_ADDRESS,

ORIGIN_TO_ADDRESS,

TO_ADDRESS,

SYMBOL,

AMOUNT_USD

from avalanche.core.ez_token_transfers

where BLOCK_TIMESTAMP >= current_date - 30

and amount_usd is not null

and SYMBOL in ('USDC', 'USDT')

),

Inflow as (

select

date_trunc(day, BLOCK_TIMESTAMP)::date as date,

sum(amount_usd) as "Inflow Volume (USD)"

from ez_transfer a join cex_address b on a.TO_ADDRESS = b.address

where block_timestamp < current_date

group by 1

),

outflow as (

select

date_trunc(day, BLOCK_TIMESTAMP)::date as date,

sum(amount_usd) as "Outflow Volume (USD)"

from ez_transfer a join cex_address b on a.ORIGIN_FROM_ADDRESS = b.address

where block_timestamp < current_date

group by 1