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::date as tranf_date,

tx_hash,

to_ADDRESS as destination,

CONTRACT_ADDRESS,

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 symbol != 'SAITAMA'

and BLOCK_TIMESTAMP::DATE >= CURRENT_DATE - 180

UNION

SELECT

BLOCK_TIMESTAMP::date as tranf_date,

tx_hash,

ETH_TO_ADDRESS as destination,

'0xC02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2' as CONTRACT_ADDRESS,

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