In this dashboard, Ethereum, Polygon, Arbitrum, Optimism, Avalanche, and Binance Smart Chain are reviewed.

To find the deposit addresses of CEXs we use data from [xxxx.core.dim_labels] table related to each chain.

For example:
We use the following query to find the deposit addresses of different CEXs on Ethereum:

SELECT ADDRESS, LABEL AS CEX 
FROM ethereum.core.dim_labels
 WHERE LABEL_TYPE = 'cex' AND LABEL_SUBTYPE = 'deposit_wallet'

To track the flow of ETH and BTC transfers to CEXs we use data from [xxx.core.ez_eth_transfers] and [xxx.core.ez_token_transfers] tables related to each chain.

The address of the sender of BTC and ETH in some transactions is 0x0000000000000000000000000000000000000000, we ignore these records.
The address of the sender of BTC and ETH in some transactions is the address of the deposit wallets of CEXs or the addresses of the hot wallets of CEXs, we ignore these records.

For example:
We use the following query to track the flows of ETH from Ethereum to CEXs:

WITH GET_INPUT_TIMESPAN AS (
  SELECT IFF(TRY_CAST( '{{LAST_X_days}}' as int) IS NULL, 5, TRY_CAST( '{{LAST_X_days}}' AS int)) AS TIMESPAN
),
GET_THRESHOLD AS (
  SELECT IFF(TRY_CAST( '{{Equal_or_greater_than_X_ETH}}' as int) IS NULL, 1000, TRY_CAST( '{{Equal_or_greater_than_X_ETH}}' AS int)) AS ETH_AMOUNT
),
CEX_WALLETS_ON_ETEHREUM AS (
SELECT ADDRESS, LABEL AS CEX FROM ethereum.core.dim_labels
 WHERE LABEL_TYPE = 'cex' AND LABEL_SUBTYPE = 'deposit_wallet'
),
ETEHREUM_TRANSACTIONS_ETH AS (
SELECT TX_HASH, DATE_TRUNC('HOUR', BLOCK_TIMESTAMP) AS TS, ETH_FROM_ADDRESS AS SENDER, ADDRESS AS "Deposit address", CEX, AMOUNT, 'ETH' AS CURRENCY, 'Ethereum' AS CHAIN 
FROM ethereum.core.ez_eth_transfers TR JOIN CEX_WALLETS_ON_ETEHREUM ON (ETH_TO_ADDRESS = ADDRESS)
WHERE AMOUNT IS NOT NULL AND AMOUNT >= (SELECT ETH_AMOUNT FROM GET_THRESHOLD) AND BLOCK_TIMESTAMP >= CURRENT_DATE - (SELECT TIMESPAN FROM  GET_INPUT_TIMESPAN)  AND SENDER != '0x0000000000000000000000000000000000000000'
UNION
SELECT TX_HASH, DATE_TRUNC('HOUR', BLOCK_TIMESTAMP) AS TS, FROM_ADDRESS AS SENDER, ADDRESS AS "Deposit address", CEX, AMOUNT, 'ETH' AS CURRENCY, 'Ethereum' AS CHAIN 
FROM ethereum.core.ez_token_transfers TR JOIN CEX_WALLETS_ON_ETEHREUM ON (TO_ADDRESS = ADDRESS)
WHERE SYMBOL = 'WETH' AND CONTRACT_ADDRESS = '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2' AND  AMOUNT IS NOT NULL AND AMOUNT >= (SELECT ETH_AMOUNT FROM GET_THRESHOLD) AND BLOCK_TIMESTAMP >= CURRENT_DATE - (SELECT TIMESPAN FROM  GET_INPUT_TIMESPAN)  AND SENDER != '0x0000000000000000000000000000000000000000'
)

SELECT TR.* 
FROM ETEHREUM_TRANSACTIONS_ETH TR JOIN ethereum.core.fact_transactions USING(TX_HASH) 
WHERE STATUS = 'SUCCESS' AND SENDER NOT IN (SELECT ADDRESS FROM ethereum.core.dim_labels WHERE LABEL_TYPE = 'cex' AND LABEL_SUBTYPE IN('deposit_wallet', 'hot_wallet'))

ETH and BTC sell-off