两列上不同值的SQL计数

Question

我有以下查询，可以通过Flipside API汇总每天唯一卖家/买家的数量：

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

现在，我已经尝试了很多不同的方法，但我一辈子都想不出如何在某一天获得唯一的活动地址数量，因为我需要以某种方式合并卖家和买家，然后计算唯一的地址。如果有任何帮助，我将不胜感激。提前感谢！

Answer 1

这就是我通过对unique_active使用单独的查询并合并它们来解决问题的方法：

WITH
other_values AS (
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
),
unique_addresses AS (
SELECT
date,
COUNT(*) as unique_active
FROM (
SELECT
date_trunc('day', block_timestamp) as date,
seller_address as address
FROM  ethereum.core.ez_nft_sales
GROUP BY date, seller_address
UNION
SELECT
date_trunc('day', block_timestamp) as date,
buyer_address as address
FROM  ethereum.core.ez_nft_sales
GROUP BY date, buyer_address
)
GROUP BY date
)
SELECT * FROM other_values
LEFT JOIN unique_addresses
ON other_values.date = unique_addresses.date
ORDER BY other_values.date DESC