Count distinct per group
Unique users per day, not just rows per day: COUNT(DISTINCT user_id) GROUP BY day, and how that differs from one distinct count overall.
Last updated
SELECT day, COUNT(*) AS events, COUNT(DISTINCT user_id) AS unique_users FROM events GROUP BY day ORDER BY day;
Why it’s written this way
COUNT(*) counts every row in the group, including a user who fired ten events that day ten separate times; COUNT(DISTINCT user_id) instead deduplicates user_id within each group before counting, so that same user contributes exactly once to unique_users. Both aggregates run over the same GROUP BY day, which is what makes them per-day figures rather than table-wide ones.
COUNT(DISTINCT user_id) GROUP BY day is not the same question as COUNT(DISTINCT user_id) with no GROUP BY: the ungrouped version counts how many distinct users appear anywhere in the whole table, while the grouped version restarts the distinct count inside each day, so a user active on three different days is counted three times, once per day, not once overall.
Edge cases to know
- →NULL user_id values are excluded from COUNT(DISTINCT ...) the same way NULLs are excluded from COUNT(column) — an anonymous-event row won't register as a 'unique user' at all, which may or may not match what the question intended.
- →Multiple COUNT(DISTINCT ...) expressions over different columns in one query (adding COUNT(DISTINCT session_id), say) run fine in DuckDB and Postgres, but some engines restrict or slow down noticeably with more than one per query.
- →COUNT(DISTINCT ...) needs an internal sort or hash-dedupe per group, so on a high-cardinality column over a large table it's measurably more expensive than a plain COUNT(*) — worth knowing before running it over an ungrouped, table-wide query.