如何统计每日唯一活跃地址？Flipside API的SQL查询求助

要合并卖家与买家地址后去重统计每日唯一活跃地址，可通过以下两种方式实现：

方式一：单独统计每日活跃地址 #

逻辑清晰，性能高效：

SELECT
  date,
  COUNT(DISTINCT address) AS unique_active_addresses
FROM (
  -- 拆分出每日所有卖家地址
  SELECT
    date_trunc('day', block_timestamp) AS date,
    seller_address AS address
  FROM ethereum.core.ez_nft_sales
  UNION ALL
  -- 拆分出每日所有买家地址
  SELECT
    date_trunc('day', block_timestamp) AS date,
    buyer_address AS address
  FROM ethereum.core.ez_nft_sales
) AS daily_addresses
GROUP BY date
ORDER BY date;

方式二：同时保留卖家、买家数与活跃地址数 #

在原有查询基础上扩展，一次性获取所有需要的统计数据：

SELECT
  date,
  unique_sellers,
  unique_buyers,
  (
    SELECT COUNT(DISTINCT addr)
    FROM (
      SELECT seller_address AS addr 
      FROM ethereum.core.ez_nft_sales 
      WHERE date_trunc('day', block_timestamp) = daily_stats.date
      UNION
      SELECT buyer_address AS addr 
      FROM ethereum.core.ez_nft_sales 
      WHERE date_trunc('day', block_timestamp) = daily_stats.date
    ) AS combined_addrs
  ) AS unique_active_addresses
FROM (
  -- 原有的每日卖家/买家统计
  SELECT
    date_trunc('day', block_timestamp) AS date,
    COUNT(DISTINCT seller_address) AS unique_sellers,
    COUNT(DISTINCT buyer_address) AS unique_buyers
  FROM ethereum.core.ez_nft_sales
  GROUP BY date
) AS daily_stats
ORDER BY date;

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