SQL count of distinct values over two columns

Question

I have the following query that allows me to aggregate the number of unique sellers/buyers for every single day from the 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

Now, I've been trying a lot of different things, but I can't for the life of me figure out how it would be possible to get the number of unique active addresses on a given day as I would need to somehow merge the sellers and buyers and then count the unique addresses. I would greatly appreciate any kind of help. Thanks in advance!

`DISTINCT` is not a function, it's a _set quantifier_. Simply write `COUNT(DISTINCT seller_address)`, to make code clearer. — jarlh, Sep 07 '22 at 14:51

score 0 · Answer 1 · answered Sep 09 '22 at 11:56

This is how I managed to solve the issue by using a separate query for the unique_active and merging them:

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

SQL count of distinct values over two columns

1 Answers1