Untitled

WITH aggregate_transfers AS (
    SELECT
        "to" AS wallet,
        SUM(value/1e18) AS received
    FROM degen_token_base.DegenToken_evt_Transfer
    WHERE
        contract_address = 0x4ed4E862860beD51a9570b96d89aF5E1B0Efefed
    GROUP BY
        "to"
    UNION ALL
    SELECT
        "from" AS wallet,
        -SUM(value/1e18) AS received
    FROM degen_token_base.DegenToken_evt_Transfer
    WHERE
        contract_address = 0x4ed4E862860beD51a9570b96d89aF5E1B0Efefed
    GROUP BY
        "from"
), token_balances AS (
    SELECT
        wallet,
        SUM(received) AS balance
    FROM aggregate_transfers
    GROUP BY
        wallet
    HAVING
        SUM(received) > 0
), top_50_holders AS (
    SELECT
        wallet,
        balance,
        ROW_NUMBER() OVER (ORDER BY balance DESC) AS rank
    FROM token_balances
    ORDER BY
        balance DESC
    LIMIT 50
), balances_one_week_ago AS (
    SELECT
        "to" AS wallet,
        SUM(value/1e18) AS balance_one_week_ago
    FROM degen_token_base.DegenToken_evt_Transfer
    WHERE
        contract_address = 0x4ed4E862860beD51a9570b96d89aF5E1B0Efefed
        AND evt_block_time <= NOW() - interval '7' day
    GROUP BY
        "to"
), balances_24_hours_ago AS (
    SELECT
        "to" AS wallet,
        SUM(value/1e18) AS balance_24_hours_ago
    FROM degen_token_base.DegenToken_evt_Transfer
    WHERE
        contract_address = 0x4ed4E862860beD51a9570b96d89aF5E1B0Efefed
        AND evt_block_time <= NOW() - interval '24' hour
    GROUP BY
        "to"
)
SELECT
    t.rank,
    t.wallet,
    t.balance AS current_balance,
    COALESCE(b.balance_one_week_ago, 0) AS balance_one_week_ago,
    t.balance - COALESCE(b.balance_one_week_ago, 0) AS balance_difference_one_week,
    COALESCE(c.balance_24_hours_ago, 0) AS balance_24_hours_ago,
    t.balance - COALESCE(c.balance_24_hours_ago, 0) AS balance_difference_24_hours
FROM top_50_holders t
LEFT JOIN balances_one_week_ago b ON t.wallet = b.wallet
LEFT JOIN balances_24_hours_ago c ON t.wallet = c.wallet
ORDER BY
    t.rank;