如何在Dune Analytics中计算BTC盈利供应占比？结果异常排查

你当前代码的核心问题 #

1. 标的完全错误：你要计算的是BTC的指标，但代码里筛选的是ETH/stETH/WETH，完全偏离目标。
2. 数据来源选错：dex.trades仅记录去中心化交易所的交易，BTC的流通供应由链上未花费UTXO（未花费交易输出）构成，大量BTC转移发生在比特币主链而非DEX，这个表根本无法覆盖所有BTC的转移情况。
3. 逻辑完全不符合指标定义：盈利供应占比要求统计现存代币中最后一次转移时价格低于当前价格的总量，但你的代码只统计了2023-10-15当天在DEX买入且价格低于当前的量，既没考虑这些代币后续是否被花费（已花费的代币不属于现存供应），也没覆盖所有链上的BTC转移行为。

正确的计算思路与代码框架 #

BTC的盈利供应占比必须基于比特币主链的UTXO数据追踪，Dune提供了bitcoin.transactions、bitcoin.outputs、bitcoin.inputs表用于处理UTXO，结合prices.usd表获取价格数据，核心逻辑如下：

1. 筛选出目标日期（2023-10-15）当天未被花费的UTXO（这些才是流通供应的组成部分）。
2. 获取每个UTXO被创建时（即最后一次转移时）的BTC价格，对比目标日期的BTC价格。
3. 统计盈利UTXO的总量，除以总流通供应量得到占比。

修正后的代码示例：

-- 计算2023-10-15的BTC盈利供应占比
WITH utxo_prices AS (
    SELECT
        o.output_id,
        o.value AS satoshis,
        -- 获取UTXO创建时的BTC美元价格
        p.price AS creation_price_usd,
        -- 获取2023-10-15当天的BTC美元价格
        (SELECT price FROM prices.usd WHERE symbol = 'BTC' AND date = '2023-10-15') AS target_date_price_usd
    FROM bitcoin.outputs o
    JOIN bitcoin.transactions t ON o.tx_id = t.tx_id
    -- 关联输入表，筛选未花费的UTXO
    LEFT JOIN bitcoin.inputs i ON o.output_id = i.spent_output_id
    -- 关联价格表，获取UTXO创建当天的BTC价格
    JOIN prices.usd p ON p.symbol = 'BTC' AND p.date = date_trunc('day', t.block_time)
    WHERE 
        i.spent_output_id IS NULL -- 仅保留未花费的UTXO
        AND date_trunc('day', t.block_time) <= '2023-10-15' -- 仅统计目标日期及之前创建的UTXO
),
total_circulating_supply AS (
    SELECT
        sum(satoshis) / 1e8 AS total_supply_btc -- 转换为BTC单位（1BTC=1亿聪）
    FROM utxo_prices
),
profit_supply AS (
    SELECT
        sum(satoshis) / 1e8 AS profit_supply_btc
    FROM utxo_prices
    WHERE creation_price_usd < target_date_price_usd -- 最后转移价格低于目标日期价格，即盈利
)

SELECT
    ROUND((ps.profit_supply_btc / ts.total_supply_btc) * 100, 2) AS profit_supply_percentage
FROM total_circulating_supply ts, profit_supply ps;

代码关键点说明 #

• UTXO追踪：通过bitcoin.outputs和bitcoin.inputs的关联，确保只统计未被花费的UTXO，这才是BTC真实的流通供应部分。
• 价格匹配：每个UTXO的创建时间对应到当天的BTC价格，确保对比的是该代币最后一次转移时的成本价和目标日期的市价。
• 单位转换：BTC的最小单位是聪（satoshis），需要除以1e8转换为BTC单位进行计算。

内容的提问来源于stack exchange，提问作者Công Lý