Why does COUNT(DISTINCT user_id) OVER (PARTITION BY month) fail on most engines?

## The error, and what it tells you `COUNT(DISTINCT user_id) OVER (PARTITION BY month)` looks like it should work: `COUNT(DISTINCT ...)` is legal, `COUNT(...) OVER (...)` is legal, so surely the combination is. It is not. PostgreSQL answers with "DISTINCT is not implemented for window functions"; SQL Server refuses `DISTINCT` in an aggregate that has an `OVER` clause; MySQL likewise does not support `DISTINCT` for an aggregate used as a window function. Some engines permit a narrow partition-only form, so the honest interview answer is "most engines reject it — verify on the one you are using" rather than a blanket claim. The reason is worth stating in terms of what a window aggregate is. In the grouped form, the engine has an explicit group and can maintain a set of the values it has seen for that group. In the window form the aggregate is defined *per row* over that row's frame, and frames overlap and shift from row to row. Duplicate elimination cannot be maintained incrementally the way a sum or a count can, so implementations simply do not offer it in the windowed form. ## The portable rewrite: aggregate first, then attach When you need the distinct count beside every detail row, compute it where `DISTINCT` is legal — a grouped query — and join it back: ```sql WITH per_month AS ( SELECT month_key, COUNT(DISTINCT user_id) AS distinct_users FROM events GROUP BY month_key ) SELECT e.event_id, e.month_key, e.user_id, m.distinct_users FROM events e JOIN per_month m ON m.month_key = e.month_key; ``` This says exactly what it means, works on every engine, and is easy to check: run the CTE alone and read the counts. If the query only needs one row per month, drop the join and use the CTE's result directly. ## The DENSE_RANK trick When the rewrite is impossible — for instance the surrounding query must stay a single pass over one row set — there is a classic window-only identity: ```sql SELECT month_key, user_id, DENSE_RANK() OVER (PARTITION BY month_key ORDER BY user_id) + DENSE_RANK() OVER (PARTITION BY month_key ORDER BY user_id DESC) - 1 AS distinct_users_in_month FROM events; ``` It works because `DENSE_RANK` assigns consecutive numbers to *distinct* values: ranking ascending gives a value's position from the low end, ranking descending gives its position from the high end, and for any value in a set of *d* distinct values those two positions sum to *d + 1*. Subtracting one leaves *d*, the same number on every row of the partition. Two caveats. First, `DENSE_RANK` treats NULL as an ordinary value that occupies one rank, whereas `COUNT(DISTINCT user_id)` ignores NULLs entirely — so the trick over-counts by one when the partition contains NULLs, unless you exclude them. Second, it is opaque; anyone maintaining the query will need the comment that explains it, so prefer the grouped rewrite unless you truly cannot use one. ## Running distinct counts are harder still "Distinct users up to and including this day" — a cumulative distinct count — has no window-aggregate form at all, and the `DENSE_RANK` identity does not extend to it because the identity holds over a whole partition, not over a growing frame. The standard approach is to reduce each user to their first occurrence, then run an ordinary running total over those first occurrences: ```sql WITH firsts AS ( SELECT user_id, MIN(event_day) AS first_day FROM events GROUP BY user_id ) SELECT first_day, COUNT(*) OVER (ORDER BY first_day) AS cumulative_distinct_users FROM firsts; ``` Each user contributes exactly one row, so a plain cumulative `COUNT(*)` is already a distinct count. ## What a good answer sounds like Name the restriction, say why the windowed form cannot maintain duplicate elimination across shifting frames, offer the grouped rewrite as the default, and keep the `DENSE_RANK` identity as the thing you reach for only when the query shape forces it — mentioning its NULL behaviour unprompted. ## Common mistakes - Assuming the combination works because both halves do. - "Fixing" it by dropping `DISTINCT`, which silently counts rows instead of users. - Adding `SELECT DISTINCT` to the outer query, which dedups output rows and does not change what the window counted. - Using the `DENSE_RANK` identity without excluding NULLs. - Assuming the identity also yields a running distinct count.