Advanced SQL: Window Functions, CTEs, and Subqueries Questions

Direct answer: For each user, number the activity dates in order with ROW_NUMBER(), then subtract that row number (in days) from the actual date. Within one unbroken run of consecutive days, the date increases by exactly 1 each row while the row number also increases by exactly 1, so date - row_number is a constant for the entire run and jumps to a new constant the moment there's a gap. That constant is a ready-made, stable group id: group by it (per user) and aggregate to get each streak's start, end, and length.

Structured elaboration

Why the trick works, concretely. If a user is active on Jan 1, 2, 3 (three consecutive days), their row numbers are 1, 2, 3. date - row_number, expressed as date - (row_number * INTERVAL 1 day) so both sides are dates, gives Dec 31, Dec 31, Dec 31 for all three rows: the row number is climbing at exactly the same rate as the date, so the difference is invariant. The moment there's a gap (say the next activity is Jan 5, skipping Jan 4), the row number continues climbing by 1 (to 4) but the date jumps by 2, so date - row_number shifts to a new constant. Every row in a contiguous run shares one constant; every gap produces a new constant. That is why grouping by this value is safe and deterministic, unlike an arbitrary running counter that would need a separate flag-and-cumsum step (the LAG-based alternative below does exactly that instead).

WITH numbered AS (
  SELECT user_id, activity_date,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY activity_date) AS rn
  FROM activity
),
grouped AS (
  SELECT user_id, activity_date,
    activity_date - (rn * INTERVAL '1 day') AS island_id
  FROM numbered
)
SELECT user_id, island_id,
  MIN(activity_date) AS streak_start, MAX(activity_date) AS streak_end, COUNT(*) AS streak_length
FROM grouped
GROUP BY user_id, island_id
ORDER BY user_id, streak_start;

LAG-based equivalent. Instead of arithmetic on the date, compare each row directly to the previous one: flag a new streak whenever activity_date <> prev_date + 1, then take a running SUM of that flag as the group id. This produces the identical grouping, at the cost of one extra window pass; it generalizes more naturally when the gap rule is not a fixed "+1 day" (see below).

Worked example (executed in DuckDB). User 1's activity dates: Jan 1, 2, 3 (a 3-day streak), then Jan 5, 6 (a 2-day streak after a 1-day gap), then Jan 10 (an isolated day).

user_id | streak_start | streak_end | streak_length
1       | 2025-01-01   | 2025-01-03 | 3
1       | 2025-01-05   | 2025-01-06 | 2
1       | 2025-01-10   | 2025-01-10 | 1

The island_id values produced internally were three distinct dates (one per run), confirming the arithmetic correctly separated the three streaks without any explicit gap-detection logic.

Generalizing the same island logic

• Coarser granularity (3+ consecutive weeks). Replace "day" with "week": truncate each activity date to its week start (e.g. date_trunc('week', activity_date)), dedupe to one row per (user, week), then apply the identical date - row_number trick using INTERVAL '1 week' instead of '1 day'. The mechanism is unchanged; only the unit of contiguity changes.
• A per-user variable gap threshold. If "consecutive" means something other than a fixed 1-day gap per user (e.g. some users are only expected to be active every other day), the date-minus-row-number arithmetic trick stops applying cleanly, because it depends on the gap being a fixed, known constant. Switch to the LAG-based form and compare against a per-user threshold column instead of a literal + 1: CASE WHEN activity_date > prev_date + gap_threshold THEN 1 ELSE 0 END.
• A tolerance window on the contiguity test. If a single missed day should still count as "the same streak" (a grace-day rule), change the LAG comparison from <> prev_date + 1 to > prev_date + tolerance_days, i.e. only break the streak when the gap exceeds the tolerance, not on any gap at all.
• The same pattern on a non-boolean series. The identical island logic applies to "3+ consecutive days of declining revenue" or "consecutive growing-revenue days": instead of flagging by date contiguity, flag each row by CASE WHEN revenue < LAG(revenue) OVER (...) THEN 1 ELSE 0 END (a direction change breaks the streak) and take the running SUM of direction-changes as the group id. The grouping mechanism (a monotonically non-decreasing counter that only increments at a boundary) is exactly the same; only the definition of "boundary" changes.

Trade-offs & pitfalls

• Deduplicate same-day activity before ranking (GROUP BY user_id, activity_date first); otherwise a duplicate row inflates streak_length without representing a real extra day.
• The date-minus-row-number trick specifically needs a fixed, known step size (1 day, 1 week); once the gap rule is conditional or per-user, fall back to the LAG-and-cumulative-sum form, which handles any boundary condition you can express as a boolean.
• date - row_number only produces a stable id within one user's partition; always include user_id in the final GROUP BY, or two different users' unrelated streaks that happen to land on the same constant will merge.

A correlated subquery references a column from the outer query inside its own WHERE (or other) clause, so conceptually the database re-runs it once per outer row, substituting that row's values each time. A non-correlated subquery has no reference back to the outer query at all, so it's evaluated once, independent of how many outer rows exist, and its result is reused for every row. Using employees(emp_id, department_id, salary), a correlated subquery can put each employee's department average right next to their own salary; a non-correlated subquery is the right tool for a single, outer-row-independent value like the company-wide average.

Correlated example: salary next to department average

SELECT
  e.emp_id,
  e.department_id,
  e.salary,
  (
    SELECT AVG(d.salary)
    FROM employees d
    WHERE d.department_id = e.department_id   -- correlated: references the outer row
  ) AS dept_avg_salary
FROM employees e
ORDER BY e.department_id, e.emp_id;

The inner query's WHERE d.department_id = e.department_id is what makes this correlated: e comes from the outer query, so the average has to be recomputed (conceptually) for every distinct department that appears in the outer rows.

Non-correlated example: a different, single-value comparison

SELECT emp_id, department_id, salary
FROM employees
WHERE salary > (
  SELECT AVG(salary) FROM employees   -- non-correlated: no reference to the outer row
);

The inner SELECT AVG(salary) FROM employees never mentions the outer table, so it produces one number, computed once, and every outer row is compared against that same value.

Key points

• The defining test is a reference, not the shape: a subquery becomes correlated the moment it uses a column from an outer table in its own filter or expression.
• A correlated scalar subquery in the SELECT list (as above) must return at most one row per outer row, or the database raises an error at runtime; the department-average query is safe because AVG() always collapses to one value.
• Real engines don't literally re-execute a correlated subquery row by row in every plan; a good optimizer can often rewrite it into a join or semi-join internally. "Conceptually re-runs per row" describes the logical semantics you should reason about when writing the query, not a guarantee about the physical execution plan.

Complexity

Logically, a correlated subquery costs O(rows in outer query) times O(cost of one inner-query execution). Whether that's actually slow depends entirely on whether the optimizer can turn it into a join or hash-based plan instead of a literal nested loop; an unindexed correlated lookup against a large table is the case where the naive per-row cost is real and painful. The non-correlated subquery costs O(1) execution of the inner query plus O(rows) for the outer comparison, since the inner result is just a constant once computed.

Worked example

With five employees across two departments (department 10: salaries 60000, 80000, 70000; department 20: salaries 50000, 90000), the correlated query above gives every department-10 employee a dept_avg_salary of 70000 and every department-20 employee 70000 as well (their department average happens to also be 70000: (50000+90000)/2). The non-correlated query, filtering against the company-wide average of 70000 ((60000+80000+70000+50000+90000)/5), returns exactly the two employees earning above it: the 80000 earner in department 10 and the 90000 earner in department 20. (Verified by executing both queries against SQLite 3.51 with this exact data.)

Trade-offs and pitfalls

For simple, single-value lookups and department-style row-level comparisons, either form works and correlated subqueries are often the most readable option. At production scale, prefer rewriting the correlated form as a JOIN against a pre-aggregated GROUP BY subquery or CTE (common table expression), or as an equivalent window function (AVG(salary) OVER (PARTITION BY department_id)), especially once you've checked EXPLAIN and confirmed the optimizer isn't already rewriting it for you; large, unindexed correlated subqueries are the classic case where a query silently degrades from fast in a small test dataset to slow in production.

A user's activity window (first_activity_date to last_activity_date) needs to become one row per calendar day, with days that had no events showing a count of 0, because sliding-window features (7-day rolling counts, day-over-day deltas) need a complete, gap-free daily grid to aggregate over. The two standard ways to build that grid are a recursive CTE (a common table expression, or CTE, that references itself to build up rows step by step) that walks the date range one day at a time, and a set-returning series function (generate_series in PostgreSQL) that produces the same grid in one call without recursion.

Approach

1. Aggregate raw events to one row per user per day.
2. Build the full day grid per user from first_activity_date to last_activity_date.
3. Left join the grid to the daily aggregate and COALESCE missing counts to 0.

-- 1. Aggregate events to one row per user per day
WITH event_day AS (
  SELECT
    user_id,
    CAST(event_ts AS date) AS day,
    COUNT(*) AS event_count
  FROM events
  GROUP BY user_id, CAST(event_ts AS date)
),
-- 2. Recursive CTE: walk each user's date range one day at a time
user_days AS (
  SELECT user_id, first_activity_date AS day, last_activity_date AS last_day
  FROM users
  WHERE first_activity_date IS NOT NULL AND last_activity_date IS NOT NULL

  UNION ALL

  SELECT user_id, day + INTERVAL '1 day', last_day
  FROM user_days
  WHERE day < last_day
)
-- 3. Left join to fill zero-count days
SELECT
  ud.user_id,
  ud.day,
  COALESCE(ed.event_count, 0) AS event_count
FROM user_days ud
LEFT JOIN event_day ed
  ON ud.user_id = ed.user_id AND ud.day = ed.day
ORDER BY ud.user_id, ud.day;

The WITH clause here must be written WITH RECURSIVE in PostgreSQL and MySQL 8+, because user_days refers to itself in its own definition. SQL Server's syntax has no RECURSIVE keyword at all; a self-referencing CTE there is just WITH cte_name AS (...) and the engine detects the recursion automatically.

Non-recursive alternative. Most analytics engines (PostgreSQL, and equivalents in Snowflake, BigQuery, Redshift) ship a series-generating function that replaces the whole recursive block:

SELECT
  u.user_id,
  d.day::date,
  COALESCE(ed.event_count, 0) AS event_count
FROM users u
CROSS JOIN LATERAL generate_series(
  u.first_activity_date, u.last_activity_date, INTERVAL '1 day'
) AS d(day)
LEFT JOIN event_day ed
  ON ed.user_id = u.user_id AND ed.day = d.day::date;

LATERAL here is technically optional: PostgreSQL lets a function call in FROM reference an earlier table's columns (u.first_activity_date) without the keyword, because that permission is only required for correlated subqueries in FROM, not for correlated function calls. Writing it explicitly is still good practice for readability, and it is the form you must use in engines that don't grant functions that exception.

Key points

• CAST(event_ts AS date) (or ::date) aligns timestamps to calendar days before aggregating.
• The recursive CTE terminates because the recursive member's WHERE day < last_day stops adding rows once day reaches last_day; without that guard the recursion never ends.
• An index on events(user_id, event_ts) keeps the aggregation step cheap; the join keys (user_id, day) should also be indexed or at least sorted compatibly for a merge join.

Complexity

The recursive CTE emits exactly one row per user-day (the sum, across users, of last_activity_date - first_activity_date + 1), but generates them by iteration: each pass depends on the output of the previous pass for that user, so the work is inherently serial per user even though different users can run in parallel. generate_series produces the same number of rows but as a single set-returning call the planner can pipeline or parallelize like any other table function, with no cross-iteration dependency. Both approaches are linear in the total number of user-days produced; the difference is constant-factor overhead and how much the query planner can optimize around the operation, not asymptotic complexity.

Worked example

Two users: user 1 spans 2024-01-01 to 2024-01-04 with events on 2024-01-01 (x2) and 2024-01-03 (x1); user 2 spans 2024-01-02 to 2024-01-03 with one event on 2024-01-02. Running the recursive-CTE query above against this data produces exactly the expected 4 + 2 = 6 rows, zero-filled where no events exist:

(Verified by executing the equivalent query against SQLite 3.51, which also requires the RECURSIVE keyword and produces this exact row set.)

Edge cases

• NULL dates: a user with a null first_activity_date or last_activity_date is excluded by the WHERE ... IS NOT NULL guard in the anchor member; without it the recursive term's comparison against a null last_day is unknown and silently produces zero rows for that user, which looks like a correct result but isn't.
• last_activity_date before first_activity_date: the anchor member still emits one row (the start date), and the recursive term never fires since the guard is immediately false. That single stray row should be filtered upstream, or treated as a data-quality signal rather than swallowed silently.
• Long ranges hit engine recursion caps: SQL Server defaults MAXRECURSION to 100, so any user active for more than 100 days errors out unless you add OPTION (MAXRECURSION 0) (unlimited) or a specific higher bound. PostgreSQL has no such hard cap, but a query with millions of iteration steps can still be slow enough to hit a statement timeout, which is one of the concrete reasons to prefer generate_series for this kind of pipeline stage.

Trade-offs and pitfalls

Recursive CTEs are the portable choice: every mainstream engine supports WITH RECURSIVE (or its keyword-less SQL Server equivalent), so the same pattern works if the pipeline has to run against more than one database. But for a sliding-window feature base that runs repeatedly in production, the non-recursive series function is usually the better default: it avoids per-user serial iteration, doesn't carry a recursion-depth risk, and most analytics engines can optimize it more aggressively. A senior answer picks the recursive form only when the target engine genuinely lacks a series-generating function, and reaches for a calendar/date-dimension table (a pre-populated table of every date in the reporting range) instead of either one when the same day-grid is joined across many pipelines, since materializing it once avoids repeating either construct per query.

Clarify first
I would ask whether the product manager wants a time-based window or a calendar window. A 168-hour window means the last 7 times 24 hours from each event timestamp. A calendar window means the current day plus the previous 6 calendar days in the store's business timezone. Those are not the same at midnight boundaries.

Implementation choices

• If they want 168 hours, use a timestamp window.
• If they want calendar days, aggregate by date first, then roll up daily counts.

-- Calendar-day version
WITH daily AS (
  SELECT
    store_id,
    CAST(order_ts AS date) AS order_date,
    COUNT(*) AS orders
  FROM orders
  GROUP BY store_id, CAST(order_ts AS date)
)
SELECT
  store_id,
  order_date,
  SUM(orders) OVER (
    PARTITION BY store_id
    ORDER BY order_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS rolling_7_day_orders
FROM daily;

Boundary example
An order at 2025-01-08 00:05 UTC is inside the calendar-day window for Jan 8 through Jan 2, but in a strict 168-hour window it depends on the exact timestamp cutoff. I would document that choice in the metric definition so analysts get the same answer.

Direct answer: Compute each user's rank with RANK() so tied scores share a rank, then paginate by a cursor on the score value itself (keyset pagination), and expand the page past its nominal size whenever the cut-off score is tied with rows just past it. A plain cursor without that lookahead has the same failure as OFFSET/LIMIT: if you take exactly page_size rows below the cursor, a tie sitting right at the cut can still get split, and because the next page's cursor becomes "everything strictly below the last score shown," the split-off row is skipped by every later page too, not just misplaced. The fix is to look one step past the nominal page size, find the score value sitting there, and pull in every row tied with it, so the page can grow past page_size but a tie group is never divided across pages.

Approach

-- naive OFFSET/LIMIT pagination (breaks on ties)
SELECT user_id, score,
  RANK() OVER (ORDER BY score DESC) AS rnk
FROM scores
ORDER BY score DESC, user_id
LIMIT 3 OFFSET 3;

-- keyset pagination with boundary-tie lookahead: the page includes every
-- row tied with the row sitting at the page_size cut-off, so the page can
-- grow past page_size but a tie group is never split across pages
WITH ranked AS (
  SELECT user_id, score,
    RANK() OVER (ORDER BY score DESC) AS rnk
  FROM scores
  WHERE score < :last_score_cursor   -- omit this predicate for the first page
),
boundary AS (
  SELECT MIN(score) AS boundary_score
  FROM (SELECT score FROM ranked ORDER BY score DESC LIMIT :page_size) t
)
SELECT r.user_id, r.score, r.rnk
FROM ranked r, boundary b
WHERE r.score >= b.boundary_score
ORDER BY r.score DESC;

The boundary CTE finds the score value sitting at the page_size-th position (or, if fewer rows remain than page_size, the lowest score still available). The outer query then returns every row scoring at least that high, which pulls in any rows tied with the one at the cut point, even though that pushes the page past page_size. The next page's cursor is the lowest score value actually returned on the current page, so its WHERE score < :cursor predicate always starts at the next strictly lower distinct score, never mid-tie.

Key points

• RANK() (not ROW_NUMBER()) is what makes tied scores actually show the same rank; ROW_NUMBER() would silently assign a different number to each tied user, hiding the tie from the leaderboard's display.
• The cursor for the next page is the lowest score value returned on the current page, not a row offset; the next page's WHERE score < :cursor predicate always starts at the next lower distinct score, never mid-tie.
• A page's row count is a soft target, not an exact one: the boundary CTE is what actually enforces this, by looking at the score sitting at the page_size cut-off and pulling in every row tied with it. Without that lookahead, a bare WHERE score < :cursor ... LIMIT :page_size cursor query has exactly the same tie-splitting failure as OFFSET/LIMIT, and it is worse: once the cursor moves past a split-off row, that row is permanently unreachable, not just displaced to a later page.

Worked example

Verified in DuckDB against scores(user_id, score) = (1,100), (2,90), (3,90), (4,90), (5,80), (6,70), (7,70), (8,60), with page_size = 3:

Naive OFFSET 0 LIMIT 3 (page 1) returns users 1, 2, 3 (scores 100, 90, 90; ranks 1, 2, 2). Naive OFFSET 3 LIMIT 3 (page 2) returns users 4, 5, 6 (scores 90, 80, 70): user 4, tied at score 90 with users 2 and 3, lands on page 2 while its tie group already appeared on page 1. A client rendering "rank 2" once at the bottom of page 1 and again at the top of page 2 has effectively shown the same rank position split across two pages, which is exactly the "tie spans a page boundary" failure. A bare cursor query without the lookahead (WHERE score < :cursor ORDER BY score DESC LIMIT 3, no boundary step) makes it worse: page 1 still returns users 1, 2, 3, and page 2's cursor becomes score < 90, which returns users 5, 6, 7 and never returns user 4 at all. The union of pages 1 and 2 is {1,2,3,5,6,7}, permanently missing user 4.

The boundary-lookahead query fixes this. Page 1 (WHERE predicate omitted, page_size = 3): the boundary CTE looks at the top 3 rows by score (100, 90, 90) and finds boundary_score = 90; the outer query then returns every row with score >= 90, which is users 1, 2, 3, and 4 (scores 100, 90, 90, 90). The page grew from 3 to 4 rows specifically because the tie group at the cut point is kept whole. The next cursor is the lowest score just returned, 90.

Page 2 uses WHERE score < 90. The remaining rows are users 5, 6, 7, 8 (scores 80, 70, 70, 60); the boundary CTE looks at the top 3 of those (80, 70, 70) and finds boundary_score = 70, so the outer query returns users 5, 6, 7 (scores 80, 70, 70): the entire score=70 tie group (users 6, 7) stays together, and the page happens to land at exactly 3 rows this time because the tie group didn't straddle the cut. The next cursor is 70.

Page 3 uses WHERE score < 70 and returns only user 8 (score 60), fewer than page_size because it's the last page. The union across all three pages is {1,2,3,4,5,6,7,8}, the complete table, each user exactly once: no row is lost or duplicated.

Why naive OFFSET/LIMIT breaks down here: OFFSET counts physical output rows, with no concept of which rows are tied on the ranking key; it simply draws a line after N rows regardless of whether row N and row N+1 have identical scores. RANK() computing the display value doesn't change this: the ranking and the pagination mechanism are two separate concerns, and OFFSET/LIMIT operates purely on row position, blind to what RANK() assigned. A bare score-cursor without the boundary lookahead inherits the identical blindness. It just relocates where the cut happens; it still cuts through a tie, and because the cursor only ever moves forward, the row stranded on the wrong side of that cut is gone for good, not merely reordered.

Trade-offs & pitfalls

Keyset pagination with boundary lookahead trades a guaranteed exact page size for correctness on ties: a page can come back larger than requested (occasionally much larger, if a huge number of users are tied on one score), so client code has to treat page_size as a maximum-ish target rather than a hard contract. If a product requirement genuinely needs exactly N rows per page no matter what, the only way to keep that guarantee without splitting ties is to accept ambiguity elsewhere (e.g., breaking ties with a secondary, always-unique column like user_id, and accepting that users with identical scores are shown in an arbitrary but stable order, which is a different trade-off than the one this question asks for). Keyset pagination is also more resilient to new rows arriving between page loads than OFFSET-based pagination: since a new higher score only affects pages earlier than the cursor position, it never reshuffles rows already shown on a page a user has already viewed, avoiding the classic "duplicate or skipped row" bug that plain OFFSET pagination has under concurrent writes, independent of the tie-splitting problem this question focuses on.