WITH

binance_address as (

SELECT

address as wallet_address,

address_name as wallet_name,

project_name,

label_type,

label_subtype

FROM crosschain.core.dim_labels

WHERE project_name = 'binance'

AND label_subtype = 'hot_wallet'

),

ethereum AS(

SELECT

block_timestamp::date AS day,

COUNT(tx_hash) AS tx_count,

SUM(amount_usd) AS usd_deposited,

SUM(amount) AS token_amount,

symbol

FROM ethereum.core.ez_token_transfers

WHERE to_address IN (SELECT wallet_address FROM binance_address)

AND block_timestamp >= '2023-11-18'

GROUP BY day, symbol

UNION

SELECT

block_timestamp::date AS day,

COUNT(tx_hash) AS tx_count,

SUM(amount_usd) AS usd_deposited,

SUM(amount) AS token_amount,

'ETH' AS symbol

FROM ethereum.core.ez_eth_transfers

WHERE eth_to_address IN (SELECT wallet_address FROM binance_address)