请求修复Dune Analytics中代币持有者历史按日期查询的SQL语句

原查询的核心问题是：它只统计了当天发生过转账的账户，而非当日结束时实际持有代币的账户，这和你要的「持有者历史数据」逻辑不符。以下是修正后的查询：

WITH daily_balances AS (
    SELECT
        DATE_TRUNC('day', evt_block_time) AS date,
        account,
        SUM(CASE WHEN evt_type = 'Transfer' THEN value ELSE -value END) AS daily_change
    FROM (
        -- 转出记录：从from地址减去金额
        SELECT
            evt_block_time,
            "from" AS account,
            -CAST(value AS NUMERIC) AS value,
            'Transfer' AS evt_type
        FROM erc20."ERC20_evt_Transfer"
        WHERE contract_address = '0x430ef9263e76dae63c84292c3409d61c598e9682'
        
        UNION ALL
        
        -- 转入记录：给to地址加上金额
        SELECT
            evt_block_time,
            "to" AS account,
            CAST(value AS NUMERIC) AS value,
            'Transfer' AS evt_type
        FROM erc20."ERC20_evt_Transfer"
        WHERE contract_address = '0x430ef9263e76dae63c84292c3409d61c598e9682'
    ) AS transfers
    GROUP BY date, account
    
    -- 计算累计余额，得到每日每个地址的最终余额
), cumulative_balances AS (
    SELECT
        date,
        account,
        SUM(daily_change) OVER (PARTITION BY account ORDER BY date) AS balance
    FROM daily_balances
)

-- 统计每日余额大于0的地址数量
SELECT
    date,
    COUNT(DISTINCT account) AS num_holders
FROM cumulative_balances
WHERE balance > 0
GROUP BY date
ORDER BY date;

关键修正点： #

• 同时追踪转入和转出记录，计算每个地址的每日余额变化
• 使用窗口函数计算累计余额，确定当日结束时的实际持有情况
• 只统计余额大于0的地址，才是真正的代币持有者

内容的提问来源于stack exchange，提问作者Gray Atreides