SELECT DISTINCT address

FROM ethereum.core.dim_labels

WHERE label_type = 'cex'

),

daily_token_flows AS (

SELECT

DATE_TRUNC('day', t.block_timestamp) as date,

t.contract_address,

m.symbol,

m.name,

-- Inflows to CEX

SUM(CASE WHEN to_address IN (SELECT address FROM cex_addresses)

THEN amount * COALESCE(p.price, 0) ELSE 0 END) as daily_inflow_usd,

-- Outflows from CEX

SUM(CASE WHEN from_address IN (SELECT address FROM cex_addresses)

THEN amount * COALESCE(p.price, 0) ELSE 0 END) as daily_outflow_usd,

-- Net Flow

SUM(CASE WHEN to_address IN (SELECT address FROM cex_addresses)

THEN amount * COALESCE(p.price, 0)

WHEN from_address IN (SELECT address FROM cex_addresses)

THEN -amount * COALESCE(p.price, 0)

ELSE 0 END) as net_flow_usd

FROM ethereum.core.ez_token_transfers t

LEFT JOIN ethereum.price.ez_asset_metadata m

ON t.contract_address = m.token_address

LEFT JOIN ethereum.price.ez_prices_hourly p

ON t.contract_address = p.token_address

AND DATE_TRUNC('hour', t.block_timestamp) = p.hour

WHERE t.block_timestamp >= CURRENT_DATE - 5

AND t.amount IS NOT NULL

AND UPPER(COALESCE(m.symbol, '')) NOT LIKE '%US%'

AND UPPER(COALESCE(m.name, '')) NOT LIKE '%US%'

--AND LOWER(m.symbol) NOT LIKE 'w%' -- wrapped tokens

AND LOWER(m.name) NOT LIKE '%wrapped%'