ShapeShiftWIP - Arbi Holdings
    Updated 2024-11-05
    WITH incoming AS (
    SELECT
    to_address AS address,
    SUM(raw_amount * 1e-18) AS balance,
    MAX(SYMBOL) AS symbol
    FROM arbitrum.core.ez_token_transfers
    WHERE CONTRACT_ADDRESS = '0xf929de51D91C77E42f5090069E0AD7A09e513c73'
    GROUP BY 1
    ),
    outgoing AS (
    SELECT
    from_address AS address,
    SUM(raw_amount * -1e-18) AS balance,
    MAX(SYMBOL) AS symbol
    FROM arbitrum.core.ez_token_transfers
    WHERE CONTRACT_ADDRESS = '0xf929de51D91C77E42f5090069E0AD7A09e513c73'
    GROUP BY 1
    ),
    unioned AS (
    SELECT * FROM incoming
    UNION ALL
    SELECT * FROM outgoing
    ),
    totals AS (
    SELECT address, SUM(balance) AS balance, MAX(symbol) AS symbol
    FROM unioned
    GROUP BY 1
    )
    SELECT
    address,
    TO_CHAR(ROUND(balance), '999999999') AS formatted_balance,
    symbol
    FROM totals
    ORDER BY balance DESC;