label,

address

FROM ethereum.core.dim_labels

WHERE label_type = 'cex'

and label_subtype = 'hot_wallet'

group by 1,2

, tx as (

SELECT

BLOCK_TIMESTAMP,

tx_hash,

to_ADDRESS,

symbol,

label,

AMOUNT_USD

FROM ethereum.core.ez_token_transfers a join cefi b on a.ORIGIN_FROM_ADDRESS = b.address

WHERE ORIGIN_FROM_ADDRESS in (SELECT address FROM cefi)

and TO_ADDRESS not in (SELECT address FROM cefi)

and BLOCK_TIMESTAMP::DATE >= CURRENT_DATE - 180

UNION

SELECT

BLOCK_TIMESTAMP,

tx_hash,

ETH_TO_ADDRESS,

'ETH' symbol,

label,

AMOUNT_USD

FROM ethereum.core.ez_eth_transfers a join cefi b on a.ETH_FROM_ADDRESS = b.address

WHERE ETH_FROM_ADDRESS in (SELECT address FROM cefi)

and ETH_TO_ADDRESS not in (SELECT address FROM cefi)

and BLOCK_TIMESTAMP::DATE >= CURRENT_DATE - 180

)