Prompt and Applicable Context
An analytics table contains events(event_id, user_id, event_at, event_name, is_internal_user). event_at is a PostgreSQL timestamptz. A user is active after at least one app_open, view_dashboard, or run_report event. Internal users do not count.
Return one row for every America/New_York calendar date from June 1 through June 30, 2026. For each report date, rolling_7d_active_users is the number of distinct eligible users active on that date or any of the previous six calendar dates. June 1 therefore needs activity from May 26 through June 1, inclusive. A user with twenty events on three dates still counts once in that window.
The query must retain dates with zero users, use explicit time boundaries, and state how late-arriving events affect a previously published result. The core challenge is a rolling distinct union. It is not a rolling sum of already aggregated daily counts.
What the Interviewer Evaluates
The first signal is metric definition before syntax. A strong candidate states the qualifying events, excluded population, reporting timezone, output grain, inclusive seven-date window, and data completeness boundary. Without those, two syntactically valid queries can answer different questions.
The second signal is grain control. Raw events must first become unique (user_id, activity_date) pairs. That removes same-day duplicates but intentionally retains a user on several dates. The final window then counts the distinct union of those user sets.
The third signal is rejecting tempting shortcuts. Summing seven DAU values counts a user once per active date. ROWS BETWEEN 6 PRECEDING AND CURRENT ROW describes seven rows, not necessarily seven calendar dates, and applying it to daily counts still cannot reconstruct the cross-day distinct union.
The final signal is production judgment: scan the six-day warm-up before the requested range, preserve empty dates with a calendar spine, convert timestamps using the declared timezone, define late-event refresh semantics, and choose an exact or approximate scaling strategy deliberately.
Questions to Clarify Before Answering
- What qualifies as active? A login-only definition produces a different set from meaningful
product events. Event names and bot or internal exclusions belong in the metric contract.
- Which timezone defines a day? This answer uses
America/New_York. UTC would move events near
local midnight to a different report date.
- Is the window seven calendar dates or 168 elapsed hours? The prompt asks for local calendar dates.
A daylight-saving transition can make those seven dates contain 167 or 169 elapsed hours.
- Are both boundaries inclusive? The user set covers
report_date - 6throughreport_date.
Source timestamp filters use a half-open range to avoid double counting the next midnight.
- Must missing dates appear? Yes. Generate all thirty report dates rather than deriving dates only
from existing events.
- How complete is the event table? If events may arrive three days late, recent results are
provisional or need a stated watermark. SQL alone cannot make incomplete input final.
- Is exact distinct required? The interview query is exact. At very large scale, an approximate
mergeable-set representation may be acceptable only after its error contract is approved.
- What database and scale apply? The answer uses PostgreSQL. A warehouse may use another date-spine
function or bitmap primitive while preserving the same set semantics.
30-Second Answer Framework
“I would first define active events, exclusions, America/New_York as the day boundary, and the output grain of one row per report date. I would scan from May 26 because June 1 needs six earlier dates, convert timestamps to local dates, and deduplicate to one row per user per date. Then I would generate June 1 through June 30 with generate_series, left join each date to activity from date - 6 through that date, and count distinct users.
I would not sum daily active users because a user active on multiple days would be counted repeatedly. A six-row window also fails when dates are missing and does not create a distinct union. I would test midnight boundaries, duplicates, empty dates, and warm-up activity, then publish an as-of watermark or refresh recent dates when late events arrive.”
Step-by-Step Deep Dive
Step 1: Fix the metric contract and input range
The requested output starts June 1, but the source scan starts May 26. Reading only June rows would undercount the first six report dates. The upper source boundary is July 1 local midnight; future activity is irrelevant to a trailing window ending June 30.
Translate those local midnights into timestamptz constants in the filter. This keeps the predicate on the indexed event_at column. Casting every source timestamp to a date inside the WHERE clause can prevent a normal range index from doing useful pruning.
Step 2: Normalize events to distinct user-days
Convert each qualifying instant to its New York calendar date only after applying the bounded timestamp filter. Then group by user_id and local date. An event retry with a different row ID and twenty events from one user on one day all become one user-day.
This deduplication does not solve the final problem by itself. If the same user is active on June 1 and June 2, both user-day rows must remain available so either date's trailing window can include that user.
Step 3: Generate a complete date spine
generate_series creates every report date independently of event presence. Starting from the event table would omit an empty date, change the number of rows in a ROWS frame, and leave no zero-valued dashboard point. The spine is the authoritative output grain.
Step 4: Count the distinct union for every window
The direct exact query is:
WITH params AS (
SELECT
DATE '2026-06-01' AS report_start,
DATE '2026-06-30' AS report_end
),
activity_days AS (
SELECT
e.user_id,
(e.event_at AT TIME ZONE 'America/New_York')::date AS activity_date
FROM events AS e
WHERE e.event_at >= TIMESTAMPTZ '2026-05-26 00:00:00 America/New_York'
AND e.event_at < TIMESTAMPTZ '2026-07-01 00:00:00 America/New_York'
AND e.event_name IN ('app_open', 'view_dashboard', 'run_report')
AND e.is_internal_user = false
GROUP BY
e.user_id,
(e.event_at AT TIME ZONE 'America/New_York')::date
),
report_dates AS (
SELECT gs::date AS report_date
FROM params AS p
CROSS JOIN generate_series(
p.report_start,
p.report_end,
INTERVAL '1 day'
) AS gs
)
SELECT
d.report_date,
COUNT(DISTINCT a.user_id) AS rolling_7d_active_users
FROM report_dates AS d
LEFT JOIN activity_days AS a
ON a.activity_date BETWEEN d.report_date - 6 AND d.report_date
GROUP BY d.report_date
ORDER BY d.report_date;The left join retains empty report dates. COUNT(DISTINCT a.user_id) ignores the null produced by an unmatched left join. The interval contains exactly seven date values: the current date and six predecessors.
Step 5: Prove why the common window shortcut fails
Suppose user A is active on Monday and Tuesday, while user B is active only on Tuesday. DAU is 1 and 2, but the two-day distinct union is 2, not 3. Once daily counts replace user identity, SQL cannot discover that A appears in both days.
A row frame introduces another error. If Wednesday has no events and is absent from the input, “six preceding rows” can reach eight or more calendar days backward. A date spine fixes calendar gaps, but a rolling sum over DAU still double counts identities. The correct operation is union first, cardinality second.
Step 6: Scale without changing semantics
For moderate ranges, index raw data by event_at and reduce it to user-days before the 7x interval join. A materialized daily activity table keyed by (activity_date, user_id) avoids rescanning raw events. Partition pruning should include the warm-up dates.
For a longer report range, expand each user-day to at most seven eligible report dates, bound those dates to the requested range, and then group distinct users. This changes join shape but not the 7x worst-case expansion. Engines with exact bitmap sets can union daily user bitmaps; approximate sketches must support set union and must expose measured error. Adding daily HyperLogLog estimates is not valid because approximate cardinalities cannot be added to obtain a union.
Step 7: Define late data and verification
Publish an as_of watermark with the result. If the pipeline accepts events up to three days late, refresh at least every report date whose seven-day input window intersects the mutable data. A stable event ID helps ingestion deduplication, while the user-day grouping protects this metric from multiple qualifying events; neither replaces completeness monitoring.
Use a hand-built oracle with duplicate events, the same user on several dates, an internal user, a nonqualifying event, May 26 and May 31 warm-up events, an empty date, local-midnight instants on both sides, and a daylight-saving boundary. Compare every output date with a simple application-level set union. Run EXPLAIN (ANALYZE, BUFFERS) on production-like volume and verify source pruning, user-day cardinality, join expansion, runtime, and spill behavior.
Strong Sample Answer
“I would define the set before writing SQL: an eligible user has at least one approved product event, internal users are excluded, and a day means America/New_York. For report date D, the set is every eligible user with a local activity date between D minus six and D, inclusive. The output must contain all thirty dates.
I would filter raw timestamps from May 26 local midnight through July 1 local midnight, keeping the upper bound exclusive. After filtering, I convert to local dates and group by user and date. A generate_series spine supplies June 1 through June 30. Each spine date left joins the user-days in its trailing interval, and COUNT(DISTINCT user_id) returns the union cardinality.
I would reject SUM(DAU) because identities active on several dates repeat, and reject ROWS 6
PRECEDING because rows are not calendar dates and daily aggregation discarded identity. For scale I would materialize (activity_date, user_id), prune the warm-up range, and consider exact bitmap unions or a measured approximate set union only when exact output is not required. The result carries an as_of watermark, and late data triggers bounded recomputation.”
Common Mistakes
- Summing seven DAU values → repeat users are counted once per active date → **union user identities
across the window, then count.**
- Using
ROWS 6 PRECEDINGon sparse dates → six rows may span more than six prior dates → **generate
a complete calendar spine and express calendar boundaries.**
- Scanning only June → early June windows lose May activity → include the six-day warm-up.
- Casting
event_atin the source filter → an ordinary timestamp index may not prune efficiently →
filter with half-open timestamptz boundaries before deriving the local date.
- Counting raw events → retries and repeated use inflate users → **deduplicate to user-day and still
count distinct across the final window.**
- Deriving output dates from events → empty dates disappear → make the date spine the output grain.
- Calling recent output final → late events can change the set → **publish a watermark and refresh
affected windows.**
- Adding approximate daily cardinalities → cardinality addition cannot remove overlap → **merge a
set-capable sketch or bitmap before estimating the union.**
Follow-up Questions and Answers
Follow-up 1: What changes if the metric means the previous 168 hours?
Compare instants instead of local date values. For each report instant, use a half-open timestamp window such as (report_at - interval '168 hours', report_at], with the exact product boundary stated. Around daylight-saving transitions, this differs from seven New York calendar dates. Do not rename one definition as the other.
Follow-up 2: Can a window function solve exact rolling distinct by itself?
A window aggregate is useful when the aggregate composes from row values, such as a rolling sum. Here, the required state is a set with deletions when old dates leave the window. The direct PostgreSQL answer keeps identities and joins them to report dates. A specialized engine may expose exact bitmap-union windowing, but that is an engine feature, not a reason to sum daily counts.
Follow-up 3: How do you refresh after an event arrives three days late?
Find the event's local activity date A. It can affect report dates A through A plus six, intersected with the published range. Recompute or replace only those partitions, advance the watermark after reconciliation, and keep the operation idempotent. Refreshing only date A misses six downstream windows.
Follow-up 4: How would you segment by country?
First define whether country belongs to the event, the user's current profile, or a slowly changing profile as of activity time. That choice changes historical truth. Add the chosen country to the user-day grain, date spine, grouping, and validation oracle. A current-profile join can rewrite history when a user moves.
Follow-up 5: What if exact distinct is too expensive?
Measure the exact query first. If the approved error budget permits approximation, store one mergeable set sketch per activity date and segment, union seven sketches, and estimate once. Validate bias and relative error against exact sets for low-, normal-, and high-cardinality slices. Keep exact processing for billing, eligibility, or other decisions that do not tolerate estimation error.