freemartian2023-11-22 02:23 PM
    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
    DATE_TRUNC('HOUR',block_timestamp) AS hour,
    COUNT(tx_hash) AS tx_count,
    SUM(amount_usd) AS usd_withdrawn,
    SUM(amount) AS token_amount,
    symbol
    FROM ethereum.core.ez_token_transfers
    WHERE from_address IN (SELECT wallet_address FROM binance_address)
    AND block_timestamp >= '2023-11-18'
    GROUP BY hour, symbol
    UNION
    SELECT
    DATE_TRUNC('HOUR',block_timestamp) AS hour,
    COUNT(tx_hash) AS tx_count,
    SUM(amount_usd) AS usd_withdrawn,
    SUM(amount) AS token_amount,
    'ETH' AS symbol
    FROM ethereum.core.ez_eth_transfers
    WHERE eth_from_address IN (SELECT wallet_address FROM binance_address)
    AND block_timestamp >= '2023-11-18'