Crazzy_SidAptos: Total Number of Addresses with a Non-Zero Balance
    Updated 2024-10-13
    WITH balance_calculations AS (
    SELECT
    ADDRESS,
    SUM(CASE WHEN TYPE = 'IN' THEN AMOUNT ELSE -AMOUNT END) AS balance
    FROM (
    SELECT
    TO_ADDRESS AS ADDRESS,
    AMOUNT,
    'IN' AS TYPE
    FROM
    aptos.core.ez_native_transfers
    WHERE
    SUCCESS = TRUE
    AND BLOCK_TIMESTAMP >= DATEADD(day, -30, CURRENT_TIMESTAMP())
    UNION ALL
    SELECT
    FROM_ADDRESS AS ADDRESS,
    AMOUNT,
    'OUT' AS TYPE
    FROM
    aptos.core.ez_native_transfers
    WHERE
    SUCCESS = TRUE
    AND BLOCK_TIMESTAMP >= DATEADD(day, -30, CURRENT_TIMESTAMP())
    ) AS combined_transfers
    GROUP BY
    ADDRESS
    )
    SELECT
    COUNT(DISTINCT ADDRESS) AS total_non_zero_balance_addresses
    FROM
    balance_calculations
    WHERE
    balance <> 0;

    QueryRunArchived: QueryRun has been archived