SELECT

distinct address as cdc_wallets

FROM ethereum.core.dim_labels

WHERE address_name LIKE '%crypto.com%'

AND label_subtype ='hot_wallet' -- select hot wallet and contract

UNION

SELECT

distinct user_address as cdc_wallets

FROM ethereum.core.ez_balance_deltas

WHERE user_address IN (

'0x6262998ced04146fa42253a5c0af90ca02dfd2a3', -- Nansen CDC Hot wallets

'0x72a53cdbbcc1b9efa39c834a540550e23463aacb', -- Nansen CDC Hot wallets

'0x7758e507850da48cd47df1fb5f875c23e3340c50', -- Nansen CDC Hot wallets

'0xcffad3200574698b78f32232aa9d63eabd290703', -- Nansen CDC Hot wallets

'0x17e49502febdf7b3bd3a9842a325036d729b7654' -- Arkham CDC Wallets

)

),

all_transfers AS ( -- eth and erc20 transfers

select

block_timestamp,

tx_hash,

origin_from_address,

from_address,

to_address,

contract_address,

symbol,

amount,

amount_usd

from ethereum.core.ez_token_transfers

where block_timestamp > '{{date}}'

union

select

block_timestamp,

tx_hash,

origin_from_address,