WITH timeframe AS (

SELECT date_day AS date

FROM crosschain.core.dim_dates

WHERE date_day <= CURRENT_DATE AND date_day >= CURRENT_DATE - 500

),

asset_transfers AS (

SELECT block_timestamp,

eth_from_address AS from_address, eth_to_address AS to_address,

amount, amount_usd,

'AVAX' AS symbol

FROM avalanche.core.ez_avax_transfers

WHERE amount > 0

UNION

SELECT block_timestamp,

from_address, to_address,

amount, amount_usd,

symbol

FROM avalanche.core.ez_token_transfers

WHERE amount > 0 AND has_price --Ignore scam 0 transfers and unknown tokens

),

cex_hot_wallets AS (

SELECT address AS hot_wallet, project_name AS cex_name

FROM crosschain.core.address_labels

WHERE blockchain = 'avalanche' AND label_type = 'cex' AND label_subtype = 'hot_wallet'

),

cex_deposit_wallets AS (

SELECT address AS deposit_address, project_name AS cex_name

FROM crosschain.core.address_labels

WHERE blockchain = 'avalanche' AND label_type = 'cex' AND label_subtype = 'deposit_wallet'

),

manual_deposit_wallets AS (

SELECT DISTINCT from_address AS deposit_address, cex_name

FROM asset_transfers

LEFT JOIN cex_hot_wallets c ON c.hot_wallet = to_address