如何用SQL筛选Dune Analytics中区块链交易的孤立地址对？

以下SQL可直接在Dune Analytics中运行，筛选出符合所有条件的孤立收发交易对：

WITH sender_stats AS (
    -- 统计每个发送方的唯一接收方数量
    SELECT
        sender,
        COUNT(DISTINCT receiver) AS distinct_receivers
    FROM transactions -- 替换为你实际使用的交易表名
    GROUP BY sender
),
receiver_stats AS (
    -- 统计每个接收方的唯一发送方数量
    SELECT
        receiver,
        COUNT(DISTINCT sender) AS distinct_senders
    FROM transactions
    GROUP BY receiver
),
address_send_counts AS (
    -- 统计每个地址作为发送方的总交易数
    SELECT
        sender AS address,
        COUNT(*) AS send_count
    FROM transactions
    GROUP BY sender
)
SELECT DISTINCT
    t.sender AS A,
    t.receiver AS B
FROM transactions t
-- 筛选仅向单一地址发送交易的A
JOIN sender_stats ss ON t.sender = ss.sender
WHERE ss.distinct_receivers = 1
-- 筛选仅从单一地址接收交易的B
AND EXISTS (
    SELECT 1 FROM receiver_stats rs 
    WHERE rs.receiver = t.receiver AND rs.distinct_senders = 1
)
-- 确保B从未发起过任何交易
AND NOT EXISTS (
    SELECT 1 FROM address_send_counts asc 
    WHERE asc.address = t.receiver AND asc.send_count > 0
)
-- 验证A的唯一接收方就是当前的B
AND (
    SELECT receiver FROM transactions WHERE sender = t.sender LIMIT 1
) = t.receiver;

逻辑说明 #

• sender_stats：锁定仅向单个地址发送交易的发送方A
• receiver_stats：锁定仅从单个地址接收交易的接收方B
• address_send_counts：排除有过发送行为的B，确保B完全没有对外交易记录
• 最后验证环节确保A的唯一交易对象就是当前匹配的B，避免统计误差

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