Rolling Distinct Users in SQL: Interview Questions That Break Naive Window Queries

Rolling distinct users in SQL is a set-union problem disguised as a window-function problem. Daily distinct counts are not additive: a person active on Monday and Tuesday should count once in a Monday–Tuesday window, but SUM(daily_users) counts that person twice. A query can use a syntactically perfect window frame and still compute the wrong metric.

The safe interview answer starts by defining one output row per calendar day and the window as a half-open interval. It then counts distinct user identities across the raw or day-deduplicated events that fall inside each report day's interval. To practice the same core challenge, open Compute DAU and rolling MAU with zero days.

Rolling Distinct Users: The Direct Answer

For a seven-day rolling active-user metric on report day d, define the interval precisely: users with at least one qualifying event at or after d - 6 days and before d + 1 day, in the chosen reporting time zone. Generate every report date, including dates with no events. Join each report date to qualifying events in its interval, then apply COUNT(DISTINCT user_id) grouped by report date.

Do not sum daily distinct counts. Do not use ROWS BETWEEN 6 PRECEDING AND CURRENT ROW until you have a dense one-row-per-day series—and even then, summing the series remains wrong for distinct users. A row frame can solve additive metrics such as revenue after daily aggregation; it cannot turn overlapping user sets into a union.

The Four-Row Counterexample

Assume the reporting dates are January 1–4 and the window is the current day plus the previous two calendar days.

Day	Users active that day	Daily distinct	Correct 3-day union	Naive rolling sum
Jan 1	A, B	2	2	2
Jan 2	A, C	2	3 (A,B,C)	4
Jan 3	none	0	3 (A,B,C)	4
Jan 4	C, D	2	3 (A,C,D)	4

The wrong answer is not an off-by-one accident. It loses identity. Once Monday becomes the scalar 2, SQL cannot know whether Tuesday's two users are the same people or two new people. No later SUM frame can recover the discarded set membership.

The missing January 3 row creates a second trap. If you aggregate only event dates, ROWS BETWEEN 2 PRECEDING means “two prior result rows,” which may reach January 1 on the January 4 row. That is four calendar days. A calendar spine is therefore part of the metric contract, not display polish.

Define the Metric Before Choosing SQL

Ask these questions:

1. What event makes a user active: any event, a login, or a named product action?
2. Is the window seven calendar dates, 168 hours, or seven business days?
3. Which time zone assigns timestamps to report dates?
4. Should anonymous and authenticated identities be merged, and by which durable key?
5. Must dates with zero activity appear?
6. Is the result exact, or is a documented approximation acceptable at scale?

Official facts: PostgreSQL documents generate_series for producing date or timestamp sequences. BigQuery documents that a ROWS frame uses physical offsets while a RANGE frame uses a logical range on the ordering key; it also documents that DISTINCT cannot be combined with OVER in GoogleSQL window-function calls. BigQuery separately recommends approximate aggregation when the use case tolerates it.

Candidate-report boundary: there is no reliable public candidate-report corpus proving a universal “rolling distinct users” interview format.

Editorial inference: the exact PostgreSQL query, test fixture, performance ladder, and explanation order below are recommended solutions for the stated contract. They are not claims about one employer's expected implementation.

Exact PostgreSQL Solution

Suppose events(user_id, event_ts, event_name) stores event_ts as PostgreSQL timestamptz. The business wants an exact three-day rolling count for January 1–4 in UTC, where active events qualify.

WITH params AS (
  SELECT
    DATE '2026-01-01' AS start_date,
    DATE '2026-01-04' AS end_date,
    3 AS window_days
),
calendar AS (
  SELECT gs::date AS report_date
  FROM params AS p
  CROSS JOIN LATERAL generate_series(
    p.start_date,
    p.end_date,
    INTERVAL '1 day'
  ) AS gs
),
user_days AS (
  SELECT DISTINCT
    e.user_id,
    (e.event_ts AT TIME ZONE 'UTC')::date AS activity_date
  FROM events AS e
  CROSS JOIN params AS p
  WHERE e.event_name = 'active'
    AND e.event_ts >= (p.start_date::timestamp AT TIME ZONE 'UTC')
                       - (p.window_days - 1) * INTERVAL '1 day'
    AND e.event_ts <  ((p.end_date + 1)::timestamp AT TIME ZONE 'UTC')
)
SELECT
  c.report_date,
  COUNT(DISTINCT ud.user_id) AS rolling_3d_users
FROM calendar AS c
CROSS JOIN params AS p
LEFT JOIN user_days AS ud
  ON ud.activity_date >= c.report_date - (p.window_days - 1)
 AND ud.activity_date <= c.report_date
GROUP BY c.report_date
ORDER BY c.report_date;

calendar guarantees all requested dates. user_days reduces repeated events from the same user on the same day, which can shrink the join input without changing the exact result. The final join reconstructs the membership relation between report dates and user-days; only then does COUNT(DISTINCT) collapse each user once per report date.

The query deliberately reads enough history before start_date to populate the first output window. Filtering raw events to the output range alone would undercount January 1. In production, pass dates and the time zone as validated parameters rather than concatenating strings.

Why the Tempting Window Query Fails

This query is wrong for rolling distinct users:

WITH daily AS (
  SELECT event_ts::date AS day,
         COUNT(DISTINCT user_id) AS dau
  FROM events
  GROUP BY event_ts::date
)
SELECT day,
       SUM(dau) OVER (
         ORDER BY day
         ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
       ) AS rolling_users
FROM daily;

It fails for two independent reasons. First, dau values overlap in user identity and cannot be added. Second, missing dates make physical row offsets diverge from calendar intervals. Filling the calendar fixes only the second problem.

A direct COUNT(DISTINCT user_id) OVER (...) is not a portable repair. Some engines reject distinct aggregation in analytic windows; BigQuery's current GoogleSQL window documentation explicitly says DISTINCT cannot be used with OVER. Even where a dialect accepts a form of windowed distinct, verify whether the frame represents calendar time and whether the engine can execute it efficiently.

A Second Exact Pattern: Expand Membership Forward

Another exact approach takes each unique (user_id, activity_date) and emits the report dates whose windows include it:

WITH user_days AS (
  SELECT DISTINCT
    user_id,
    (event_ts AT TIME ZONE 'UTC')::date AS activity_date
  FROM events
  WHERE event_name = 'active'
),
expanded AS (
  SELECT
    ud.user_id,
    gs::date AS report_date
  FROM user_days AS ud
  CROSS JOIN LATERAL generate_series(
    ud.activity_date,
    ud.activity_date + 2,
    INTERVAL '1 day'
  ) AS gs
)
SELECT report_date,
       COUNT(DISTINCT user_id) AS rolling_3d_users
FROM expanded
GROUP BY report_date
ORDER BY report_date;

Restrict the output dates and input history in a real query. This pattern can be easier to explain because each active user-day contributes to exactly three candidate report dates. Its cost grows with user_days × window_days, so it is not automatically faster. The first range-join pattern and this expansion pattern are logically equivalent for the stated daily contract.

Test the Query with Set-Level Properties

Use the four-row fixture, then add these cases:

• one user fires 100 events on one day;
• the same user appears on every day;
• a report date has no events;
• an event lands exactly at midnight;
• the first report date needs events from before the displayed range;
• user_id is NULL or an anonymous identifier changes after login;
• duplicate ingestion produces identical physical rows;
• the window length is one day.

Check properties, not only snapshots. The rolling count cannot exceed the distinct users in the full input interval. Repeating an identical event must not change the result. With a one-day window, the result must equal DAU on the dense calendar. Removing one user's only in-window event can decrease counts only for report dates whose windows contained that event.

For the sample, calculate the expected sets by hand. If the SQL reports 4 for January 2, you immediately know it summed memberships rather than unioning identities.

Time Zones and Half-Open Boundaries

A calendar date has meaning only in a time zone. If events are stored as timestamptz, convert them into the named reporting zone before extracting the date. A New York event at 2026-01-02 00:30 UTC belongs to January 1 locally during standard time. Casting in UTC first changes the metric.

For timestamp windows, write [window_start, window_end) explicitly. The end for report date d is the next local midnight, not necessarily “add 24 hours” across daylight-saving changes. PostgreSQL's time-zone-aware generate_series form documents daylight-saving-aware sequences when a time-zone argument is used. If the interview scope uses UTC dates, say so and keep the implementation simpler.

Scale: Preserve Sets or Accept Approximation

The exact range join can become expensive across billions of events and hundreds of report dates. Improve it in steps:

1. Partition raw events by event date and cluster or index by date and user identity.
2. Materialize unique user-days so repeated events disappear early.
3. Bound both the requested output dates and the required lookback input.
4. Maintain incremental state if the product repeatedly asks for the latest window, including expiration when a user's last qualifying day leaves the window.
5. If approximation is acceptable, store mergeable distinct sketches per time bucket and merge the sketches covering each window.

Sketches trade exact membership for bounded storage and faster merges. State the error contract and test it; never call an approximate count “exact MAU.” BigQuery's official guidance recommends APPROX_COUNT_DISTINCT when approximation fits the use case, but that recommendation does not choose the acceptable error or window architecture for you.

Do not merge daily scalar estimates. Merge sketch states designed for union. Likewise, exact incremental state must know each user's last qualifying day or maintain per-user bucket membership; a single counter cannot tell whether an expiring user remains active on another day inside the window.

Explain the Solution in an Interview

A concise explanation is:

Daily distinct counts throw away identity, so they cannot be summed. I generate a dense calendar, join each report date to user-days inside its calendar interval, and count distinct users after that join. I read the pre-range history needed for the first window, define the reporting time zone, and test repeated users plus missing dates. At scale, I would materialize user-days and consider mergeable sketches only if approximation is allowed.

Then draw the counterexample. It proves the core point faster than another page of SQL. If the interviewer asks for a window function, explain that window functions are appropriate for additive metrics after daily aggregation; the distinct-set union needs preserved identity or a mergeable set representation.

Final Takeaway

The decisive insight is small: distinct counts are sets, not additive numbers. Preserve user identity until after each report day's interval is formed. Generate missing dates, carry enough history into the first window, define time-zone boundaries, and verify the result with a repeated-user counterexample.

That answer is more valuable than forcing every metric through SUM(...) OVER. It shows that you understand what a window frame can preserve, what daily aggregation destroys, and which exact or approximate data structure the metric actually requires.

Sources and Further Reading

• PostgreSQL set-returning functions and generate_series
• PostgreSQL window functions
• BigQuery window-function calls and frame semantics
• BigQuery performance guidance for approximate aggregation

Official sources support the cited SQL capabilities and restrictions. The worked schema, queries, test properties, and scaling recommendations are editorial inference.