Rocky_sUSDC Inflow and Outflow on Binance
    Updated 2023-11-21
    with bnb_wallet AS (
    -- for withdrawl
    SELECT address
    FROM ethereum.core.dim_labels
    WHERE label_type = 'cex'
    AND label = 'binance'),

    usdc_deposit AS (
    SELECT date_trunc('hour', ed.BLOCK_TIMESTAMP) AS time,
    sum(ed.amount) AS total_USDC,
    count(DISTINCT ed.TO_ADDRESS) AS deposit_users,
    sum(ed.AMOUNT_USD) AS Deposit_volume,
    count(DISTINCT ed.tx_hash) AS Deposit_tx
    FROM ethereum.core.ez_token_transfers AS ed
    JOIN bnb_wallet AS db on ed.FROM_ADDRESS = db.address
    WHERE ed.BLOCK_TIMESTAMP >= DATEADD(HOUR, -24, CURRENT_TIMESTAMP())
    AND ed.contract_address = '0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48'
    AND ed.TO_ADDRESS <> db.address
    GROUP BY 1
    ORDER BY 1),

    usdc_withdrawl AS (
    SELECT date_trunc('hour', ed.BLOCK_TIMESTAMP) AS time,
    sum(ed.amount)*-1 AS total_USDC,
    count(DISTINCT ed.FROM_ADDRESS)*-1 AS withdrawl_users,
    sum(ed.AMOUNT_USD)*-1 AS withdrawl_volume,
    count(DISTINCT ed.tx_hash)*-1 AS withdrawl_tx
    FROM ethereum.core.ez_token_transfers AS ed
    JOIN bnb_wallet AS db on ed.TO_ADDRESS = db.address
    WHERE ed.BLOCK_TIMESTAMP >= DATEADD(HOUR, -24, CURRENT_TIMESTAMP())
    AND ed.contract_address = '0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48'
    AND ed.FROM_ADDRESS <> db.address
    GROUP BY 1
    ORDER BY 1),

    total AS (