Prompt and Applicable Context
You are given a PostgreSQL event table:
CREATE TABLE user_logins (
user_id bigint NOT NULL,
login_at timestamptz NOT NULL
);For every user, return every longest run of consecutive America/New_York calendar dates on which the user logged in at least once. The output columns are user_id, streak_start, streak_end, and streak_days. Multiple events on the same local date count as one active day. Any missing local date breaks a streak. If a user has two longest streaks of equal length, return both. Users with no events do not appear.
This is a data and SQL interview problem, not a request to count elapsed 24-hour periods. A local day around a daylight-saving transition may contain 23 or 25 hours and is still one calendar date. The question therefore fixes the reporting time zone before converting timestamps to dates. The main answer targets PostgreSQL; other dialects need different date arithmetic.
The core task is a gaps-and-islands problem: transform ordered dates so every date in one consecutive run shares a stable key, aggregate each key into an island, and then retain all islands tied for the maximum length per user.
What the Interviewer Evaluates
The first signal is whether the candidate defines the grain before writing a window function. The source grain is one login event, but the business grain is one row per user and local calendar date. Skipping that conversion lets duplicate same-day events inflate ROW_NUMBER(), counts, and streak boundaries.
The second signal is whether the candidate can derive the island key. After distinct dates are sorted, both the date and ROW_NUMBER() advance by one inside a consecutive run. Subtracting the row-number offset from each date therefore produces the same value throughout that run. At a gap, the date jumps by more than one while the row number advances by exactly one, so the key changes.
The third signal is contract discipline. “Longest streak” is ambiguous when two runs have the same length. A query that uses ROW_NUMBER() to choose one result silently discards a valid tie. This prompt requires every tied maximum, so the answer compares each island length with the maximum island length for that user.
The fourth signal is time-zone correctness. Casting login_at directly to date uses the database session time zone, which may differ between environments. The answer converts each timestamptz to a named business zone first and only then takes the date. A fixed UTC offset is insufficient for a zone whose offset changes with daylight-saving rules.
The final signal is verification and scale judgment. A correct query should be tested at every CTE, with duplicate events, one-day runs, gaps, ties, local-midnight cases, and daylight-saving boundaries. On a large repeatedly queried event table, the candidate should recognize that reducing raw events to one stored row per user-day can be more valuable than micro-optimizing the final window query.
Questions to Clarify Before Answering
- What defines a day? A named business time zone, UTC, or each user's own zone changes the date
conversion and possibly the answer. This prompt uses America/New_York for every user.
- Do multiple logins on one day count more than once? They do not here, so deduplication must happen
before numbering. If the metric were consecutive events instead, the grain and grouping rule would change.
- Does one missing date always break the streak? Yes. A sessionization question with a 30-minute
threshold needs previous-row comparison rather than strict calendar adjacency.
- How should ties be returned? This contract returns every tied longest island. Choosing the most
recent streak would require a different, explicit tie-breaker.
- Is the range bounded? A date filter can reduce work, but it also truncates streaks that begin
before the range. The caller must say whether the result is “within the range” or the complete streak crossing its boundary.
- May
user_idorlogin_atbe null? The schema says no. If nulls were allowed, their treatment
would need to be specified before ordering or grouping.
- Is this a one-off query or a recurring product metric? An ad hoc answer can scan and sort daily
rows. A frequently refreshed dashboard may justify an incrementally maintained user-day table.
30-Second Answer Framework
“I would first convert each timestamptz into the agreed business time zone and deduplicate to one row per user and local date. Within each user, I order those dates and assign ROW_NUMBER(). For strict consecutive dates, login_day - row_number × one day stays constant inside a streak and changes after a gap, so I group by that derived key to get each streak's boundaries and length. I then compare each length with the user's maximum, which preserves ties. I would test duplicate same-day events, one-day streaks, gaps, equal maxima, local-midnight and daylight-saving cases, and inspect the plan on representative data.”
Step-by-Step Deep Dive
Start by normalizing the event stream to the business grain. For a timestamptz, AT TIME ZONE with a named zone produces the wall-clock timestamp in that zone. Casting that result to date gives the business calendar date. SELECT DISTINCT then guarantees exactly one row per user-day.
The complete query is:
WITH login_days AS (
SELECT DISTINCT
user_id,
(login_at AT TIME ZONE 'America/New_York')::date AS login_day
FROM user_logins
),
numbered AS (
SELECT
user_id,
login_day,
ROW_NUMBER() OVER (
PARTITION BY user_id
ORDER BY login_day
) AS rn
FROM login_days
),
grouped AS (
SELECT
user_id,
login_day,
login_day - (rn * INTERVAL '1 day') AS island_key
FROM numbered
),
streaks AS (
SELECT
user_id,
MIN(login_day) AS streak_start,
MAX(login_day) AS streak_end,
COUNT(*) AS streak_days
FROM grouped
GROUP BY user_id, island_key
),
scored AS (
SELECT
user_id,
streak_start,
streak_end,
streak_days,
MAX(streak_days) OVER (PARTITION BY user_id) AS max_streak_days
FROM streaks
)
SELECT
user_id,
streak_start,
streak_end,
streak_days
FROM scored
WHERE streak_days = max_streak_days
ORDER BY user_id, streak_start;The proof follows the ordered daily rows. For one user, call the distinct dates d1, d2, ... and the row numbers 1, 2, .... If d(i+1) = d(i) + 1 day, then subtracting the next row-number offset removes the same extra day, so the derived keys are equal. If at least one date is missing, d(i+1) advances by two or more days while the row number advances by one; the derived key increases and starts a new group. Deduplication makes COUNT(*) equal to calendar days, while MIN and MAX are the exact island boundaries.
The final maximum is deliberately a windowed MAX, not another ROW_NUMBER(). Every island whose length equals the user's maximum survives. If the product later asks for only one streak, add a stated rule such as “latest end date wins” and use deterministic ordering; do not invent that rule inside the query.
Consider normalized dates for two users:
user 1: Mar 07, Mar 08, Mar 09, Mar 11, Mar 12
user 2: Nov 01, Nov 02, Nov 04, Nov 05
result:
user 1 | Mar 07 | Mar 09 | 3
user 2 | Nov 01 | Nov 02 | 2
user 2 | Nov 04 | Nov 05 | 2User 1 has a three-day maximum. User 2 has two separate two-day maxima, so both rows are required. Multiple raw events on any displayed date do not change the result. Around a daylight-saving change, the relevant question remains whether local dates are adjacent, not whether timestamps are exactly 24 hours apart.
For N raw events and D distinct user-day rows, deduplication reads N rows and may hash or sort; the window step orders up to D rows by user and date. A useful interview bound is O(N log N + D log D) time in a sort-based plan and O(D) intermediate space, while noting that the optimizer may use hashes, existing order, parallelism, or disk spills. The execution plan, not Big-O alone, decides whether the production query is acceptable.
For a recurring metric over billions of events, create an incrementally maintained table with a unique key on (user_id, login_day). That moves time-zone conversion and same-day deduplication to the ingestion or batch boundary, so the streak query reads D daily rows instead of N events. If an ad hoc query has a time range, apply sargable UTC timestamp bounds before local-date conversion, but derive those UTC boundaries from named-zone local midnights so daylight-saving changes are respected.
Inspect intermediate results instead of treating the final table as proof:
-- These checks are run against the corresponding CTE or materialized test result.
SELECT user_id, login_day, COUNT(*)
FROM login_days
GROUP BY user_id, login_day
HAVING COUNT(*) > 1;
SELECT *
FROM numbered
ORDER BY user_id, login_day;
SELECT *
FROM streaks
WHERE streak_days <> (streak_end - streak_start + 1);The first and third checks should return no rows. The numbered output makes a wrong grain or ordering visible. Run the complete query prefixed with EXPLAIN (ANALYZE, BUFFERS) to reveal scans, sorts, row estimates, temporary I/O, and whether reducing raw events earlier would matter. Use a safe representative copy when executing the production statement itself would be expensive.
The shifted-date technique is not universal. If a new session begins when the gap exceeds 30 minutes, or an island continues while a status value remains unchanged, use LAG() to inspect the previous row, flag each boundary, and take a running SUM() of those flags. The decision rule is simple: use the shifted key for strict unit-by-unit sequences; use boundary flags when continuity depends on a custom comparison.
High-Quality Sample Answer
“Before writing SQL, I would lock the grain and tie contract. The source has many events per user, but the metric counts one America/New_York calendar date per user. I would therefore convert the timestamptz into that named zone, cast to date, and deduplicate before any window function. This also prevents the session time zone from silently changing the result.
For the gaps-and-islands step, I assign ROW_NUMBER() ordered by local date within each user. During a consecutive run, both the date and row number advance by one, so subtracting the row-number day offset produces a constant key. A missing date makes the date jump farther than the row number and changes the key. Grouping on user and that key gives the start, end, and number of active dates for each streak.
I would use a windowed maximum over the streak lengths and keep equality with that maximum. That returns all tied longest streaks, as required, instead of silently choosing one. The sort-based upper bound is roughly O(N log N + D log D), where N is raw events and D is distinct user-days, though I would inspect the actual plan and spills.
My test data would include several events on one date, a one-day user, a missing date, two equal maxima, events on both sides of local midnight, and a daylight-saving transition. For a recurring large-scale metric, I would maintain a unique user-day table and run the window logic over that smaller grain. If continuity changes from calendar adjacency to a threshold gap, I would switch to LAG() plus boundary flags and a running sum.”
Common Mistakes
- Numbering raw login events → duplicate events advance the row number and inflate counts →
deduplicate to one user-day row before applying windows.
- Casting
timestamptzdirectly todate→ the answer depends on the session time zone →
convert to the named business zone first.
- Using a fixed UTC offset → local dates become wrong when the named zone changes offset →
use an IANA zone with its calendar rules.
- Comparing timestamps 24 hours apart → 23-hour or 25-hour local days break valid calendar streaks
→ compare local dates, because the contract is calendar adjacency.
- Grouping only by the shifted date → users with the same derived key merge together → **group by
both user_id and island_key.**
- Taking one row with
ROW_NUMBER()→ tied longest streaks are discarded → **compare every island
with the per-user maximum.**
- Filtering a reporting interval without a boundary rule → a streak crossing the start date is
truncated and may be mislabeled → define whether results are range-local or complete islands.
- Using
LAG()without handling the first row → the first island lacks a boundary → **treat a null
previous row as the start of a group.**
- Quoting only Big-O → a sort spill or poor cardinality estimate remains invisible → **inspect
intermediate counts and EXPLAIN (ANALYZE, BUFFERS).**
- Scanning raw history for every dashboard refresh → repeated conversion and deduplication dominate
cost → maintain a unique user-day grain when the workload justifies it.
Follow-Up Questions and Responses
Follow-up 1: How would you return only the most recent longest streak?
Keep the same island construction. After computing streaks, rank them per user by streak_days DESC, then streak_end DESC, and finally streak_start DESC as a deterministic last tie-breaker. Return rank one. State that this changes the output contract: equal lengths no longer all survive.
Follow-up 2: What changes if a session ends after 30 minutes of inactivity?
Calendar subtraction no longer models continuity. Order events by timestamp, use LAG(login_at) per user, mark the first row or any gap greater than 30 minutes as a new session, and compute a running SUM of that flag with an explicit ROWS UNBOUNDED PRECEDING frame. Aggregate by user and generated session ID.
Follow-up 3: How do you handle each user's own time zone?
Join the event to a versioned user-time-zone value that is valid for the event time, then convert before taking the date. A single current profile setting can rewrite historical days after a user moves. Clarify whether the product wants historical activity frozen under the then-current zone or recomputed under the user's current zone; those are different metrics.
Follow-up 4: How would you query only the last 90 local days?
Define whether a streak may start before the window. For window-local results, derive the two UTC instants corresponding to local midnight at the start and end in the named zone, filter login_at by those bounds, then normalize. For complete islands, include enough preceding daily rows to find the first actual gap; a blind 90-day cut cannot prove the true start.
Follow-up 5: How would you make this efficient for a daily dashboard?
Maintain user_login_days(user_id, login_day) with a unique key and idempotent upserts. Update it from the event pipeline using the agreed time-zone rule. Recompute only users whose daily rows changed, or periodically rebuild from an overlap window to absorb late events. Reconcile daily-row counts against the raw source before publishing.
Follow-up 6: Which tests would you require before shipping?
Use table-driven fixtures for duplicates, one-row users, internal gaps, tied maxima, local-midnight events, daylight-saving start and end, late-arriving events, and reporting-boundary crossings. Assert that daily rows are unique, every island satisfies streak_days = streak_end - streak_start + 1, and every returned streak equals its user's maximum. Compare the incremental daily table with a raw-event recomputation on sampled users, then inspect the query plan and temporary I/O at production-like cardinality.