bgbz-8j1pHvsMON holders at timestamp
    Updated 7 days ago
    WITH token_transfers AS (
    SELECT
    '0x' || SUBSTRING(TOPICS[1], 27) AS sender,
    '0x' || SUBSTRING(TOPICS[2], 27) AS receiver,
    CAST(ethereum.public.udf_hex_to_int(data) AS NUMERIC) / 1e18 AS amountx
    FROM monad.testnet.fact_event_logs
    WHERE
    topics[0] = '0xddf252ad1be2c89b69c2b068fc378daa952ba7f163c4a11628f55a4df523b3ef' -- Transfer event topic
    AND CONTRACT_ADDRESS = '0x07aabd925866e8353407e67c1d157836f7ad923e'
    AND TX_SUCCEEDED = 'TRUE'
    AND block_timestamp <= '{{timestamp}}'
    ),

    balances AS (
    SELECT
    receiver AS holder,
    SUM(amountx) AS balance
    FROM token_transfers
    GROUP BY receiver
    UNION ALL
    SELECT
    sender AS holder,
    -SUM(amountx) AS balance
    FROM token_transfers
    GROUP BY sender
    )

    SELECT
    holder AS "Holder",
    TO_VARCHAR(SUM(balance), '999,999,999,999.00') AS "Balance at Timestamp",
    FROM balances
    GROUP BY holder
    HAVING SUM(balance) >= 0.01
    ORDER BY SUM(balance) DESC;