cex_wallets AS (

SELECT address

FROM ethereum.core.dim_labels

WHERE label_type = 'cex'

AND label_subtype = 'hot_wallet'

),

daily_token_price AS (

SELECT hour::date AS utc_date

, token_address

, symbol

, price AS price_usd

FROM ethereum.core.fact_hourly_token_prices AS p

WHERE p.hour >= CURRENT_TIMESTAMP - interval '6 months'

AND price IS NOT NULL

QUALIFY row_number() OVER (partition by token_address, hour::date order by hour desc) = 1

),

cex_token_withdrawals AS (

SELECT tr.tx_hash

, tr.block_timestamp

, 'ETH' AS symbol

, p.token_address

, tr.amount

, tr.amount * p.price_usd AS amount_usd

, tr.eth_from_address AS from_address

, tr.eth_to_address AS to_address

FROM ethereum.core.ez_eth_transfers AS tr

LEFT JOIN daily_token_price AS p