Advanced SQL: Window Functions, CTEs, and Subqueries Questions

The same "latest order per user" result can be built with a correlated subquery, a derived-table join against a pre-aggregated MAX(created_at), or a ROW_NUMBER() window function; they differ in how much per-row work the engine has to redo and in how they handle ties. On a large orders table, the ROW_NUMBER() form is usually the best default because it computes ranks in one ordered pass and lets you break ties deterministically, but the correlated form is still the right call when the predicate is narrow and well-indexed.

Approach 1: correlated subquery

SELECT o.*
FROM orders o
WHERE o.created_at = (
  SELECT MAX(o2.created_at)
  FROM orders o2
  WHERE o2.user_id = o.user_id
);

Approach 2: derived-table join on MAX(created_at)

SELECT o.*
FROM orders o
JOIN (
  SELECT user_id, MAX(created_at) AS max_created
  FROM orders
  GROUP BY user_id
) m ON o.user_id = m.user_id AND o.created_at = m.max_created;

Approach 3: ROW_NUMBER() window function

SELECT order_id, user_id, amount, created_at
FROM (
  SELECT o.*,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC, order_id DESC) AS rn
  FROM orders o
) t
WHERE rn = 1;

Key points

• Approaches 1 and 2 both compare against MAX(created_at), so if two orders for the same user share the exact same timestamp, both are returned; neither query has a tiebreaker.
• Approach 3 adds order_id DESC as an explicit tiebreaker, so it always returns exactly one row per user even when created_at collides.
• ROW_NUMBER() can't be filtered directly in a WHERE clause (window functions evaluate after WHERE), which is why it has to be wrapped in a subquery, as shown, or filtered with QUALIFY in engines that support it (Snowflake, BigQuery, Databricks; not PostgreSQL or MySQL).

Complexity

The correlated subquery is, logically, one MAX lookup per outer row; whether that's cheap depends entirely on the plan. With an index on (user_id, created_at DESC), a smart optimizer can turn this into an index-descent per user rather than a full re-scan, but many optimizers instead execute it as a nested loop that repeats the inner scan once per outer row, which is the pattern to watch for in EXPLAIN. The derived-table join computes the per-user MAX once, in a single grouped pass over the table (an index or hash aggregate handles this in roughly linear time), then joins that back, which is a bounded amount of work regardless of how many orders a user has. The ROW_NUMBER() form does one sort (or an index-satisfied ordering) per partition, which is generally the same order of work as the grouped aggregate but produces the tiebreaker and the ordering guarantee in the same pass.

Worked example

With five orders across three users, including two orders for user 101 that share the exact same created_at (a same-second data-entry collision), running all three queries against this data shows the difference directly: approaches 1 and 2 both return two rows for user 101 (the tie), while approach 3 with ORDER BY created_at DESC, order_id DESC returns exactly one deterministic row per user, including user 101. (Verified by executing all three queries against SQLite 3.51 with this exact tie case.)

Trade-offs and pitfalls

Genuinely reach for the correlated form when the predicate is highly selective and backed by the right composite index (e.g., you've already filtered down to a handful of users), because a per-row indexed lookup can be cheaper than materializing a full per-user aggregate you don't need elsewhere; some optimizers (PostgreSQL, SQL Server) can automatically rewrite a correlated subquery like this into a semi-join or hash join, but that isn't guaranteed across engines or even across query shapes on the same engine, so don't assume it happened silently. Always check: if EXPLAIN shows a nested loop re-scanning orders once per outer row instead of a hash or merge join, that's the concrete signal to rewrite into the derived-table or ROW_NUMBER() form. The most common bug across all three forms isn't performance, it's silently dropped or duplicated rows from an unhandled tie; if the business logic genuinely needs "the" single latest order, the ROW_NUMBER() form with an explicit tiebreaker is the only one of the three that guarantees it.

Direct answer: Aggregate revenue per product, rank within each category with RANK() (not ROW_NUMBER(), so ties share a rank), then filter to rnk <= 3 in an outer query, never inside the same SELECT the window function runs in, since window functions cannot appear in a WHERE clause at all. For the order-of-operations bug: a window function evaluates after FROM/JOIN, WHERE, GROUP BY, and HAVING have already run, so any filter placed in that earlier WHERE clause has already shrunk (or changed) the rowset the window function sees, before the window function's own partition and ranking logic gets to run over what's left. If the intent was "rank across everyone, but only display a subset," that filter has to move to an outer query wrapped around the ranking, applied after the rank is computed, not before.

Approach: top-3-per-category with ties included

WITH product_revenue AS (
  SELECT p.product_id, p.category_id, SUM(s.revenue) AS total_revenue
  FROM products p
  JOIN sales s ON s.product_id = p.product_id
  GROUP BY p.product_id, p.category_id
),
ranked AS (
  SELECT *, RANK() OVER (PARTITION BY category_id ORDER BY total_revenue DESC) AS rnk
  FROM product_revenue
)
SELECT * FROM ranked WHERE rnk <= 3
ORDER BY category_id, rnk;

Key points

• RANK() is required, not ROW_NUMBER(): it's what lets a tie at the 3rd-place cutoff produce 4 (or more) output rows for that category instead of arbitrarily dropping one of the tied products.
• The rnk <= 3 filter has to be a WHERE on the outer query reading from the ranked common table expression (CTE, a named subquery written with WITH ... AS (...)), not folded into the same SELECT as the RANK() call; attempting WHERE RANK() OVER (...) <= 3 in the same query fails outright, since window functions are evaluated after WHERE and cannot be referenced by it.
• Verified in DuckDB against a category with revenues 100, 90, 80, 80, 10 (a genuine tie at the 3rd-place cutoff): ranks come out 1, 2, 3, 3, 5, and rnk <= 3 correctly returns 4 rows, not 3, since both rank-3 products are included.

Confirming the hard WHERE-with-window-function error directly: running ... WHERE RANK() OVER (PARTITION BY category_id ORDER BY total_revenue DESC) <= 3 in the same SELECT raises Binder Error: WHERE clause cannot contain window functions! in DuckDB (Postgres and most standard-conforming engines reject the same construct for the same reason: the standard's logical query-processing order runs WHERE before the SELECT list, where window functions live, so at the point WHERE is evaluated the window function's result doesn't exist yet).

Approach: the subtler order-of-operations bug

This second failure doesn't error; it silently changes which rows the window function's partition sees. Say the intent is "rank sales reps by total company-wide revenue, but only display West-region reps' ranks":

-- WRONG: filters to West BEFORE the window function runs, so RANK() only
-- ever sees West rows and ranks reps against each other, not against the company
SELECT rep_id, region, total_revenue,
  RANK() OVER (ORDER BY total_revenue DESC) AS company_rank
FROM rep_revenue
WHERE region = 'West';

-- CORRECT: rank over the full, unfiltered set first, then filter in an outer query
WITH ranked AS (
  SELECT rep_id, region, total_revenue,
    RANK() OVER (ORDER BY total_revenue DESC) AS company_rank
  FROM rep_revenue
)
SELECT * FROM ranked WHERE region = 'West';

Verified in DuckDB against rep_revenue = (1,West,900), (2,West,500), (3,East,800), (4,East,700), (5,West,300): the wrong version ranks rep 2 as company_rank = 2, since WHERE region = 'West' already removed the two East reps before RANK() ever ran, so it only ever compared West reps against each other. The correct version, which computes RANK() over all 5 reps first and filters afterward, gives rep 2 company_rank = 4 (correctly behind both East reps at 800 and 700), and rep 5 goes from a wrong 3 to a correct 5. Same query shape, same filter, different result, purely because of when the filter runs relative to the window function.

Key points

• This bug never errors: the query executes and returns plausible-looking numbers, which is what makes it dangerous; nothing about the output signals that the ranks were computed against a filtered subset instead of the intended full set.
• The fix is structural, not syntactic: move the filter to an outer query or a later WHERE clause that reads from a CTE or subquery where the window function has already run, so the filter only removes rows after ranking, not before it.
• A related, adjacent issue: if ORDER BY total_revenue DESC alone doesn't fully determine row order (ties, or an equivalent secondary sort key not included), the same query can return a different, equally "valid" ranking on a re-run or across replicas; add a deterministic tiebreaker column (ORDER BY total_revenue DESC, rep_id) so the ranking output is reproducible, independent of whichever physical row order the engine happened to scan in.

Edge cases

• A category (or region) with only one product/rep: ranking and filtering both degenerate correctly to that single row; no special-casing needed.
• Filtering that's actually intended to happen before the window function (e.g., WHERE order_status = 'completed' to exclude cancelled orders from the revenue base entirely) is completely correct in the same position; the bug only exists when the filter is meant to restrict the display of an already-computed ranking, not the population the ranking is computed over. Getting this distinction right is a requirements question, not a syntax one.

Trade-offs & pitfalls

The common wrong turn is treating "the query runs without error" as proof it's correct; the order-of-operations bug specifically produces plausible, wrong numbers with no error signal at all. When reviewing a ranking query, explicitly ask: is every WHERE/JOIN condition meant to change the population being ranked, or meant to filter the display of an already-computed rank? Any condition in the second category belongs in an outer query, never alongside the window function itself.

Direct answer

Partition by session_id, order by event_time, and give LAST_VALUE an explicit ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING frame so it returns the session's true last event instead of just echoing the current row. Left implicit, an ORDER BY with no frame clause defaults to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW: for any given row, that frame's upper edge stops at the row itself, so the window has not yet seen any of the rows that come after it in the session. LAST_VALUE can only return the last value inside the frame it is given, and on every row the frame ends at that row, which is why it echoes the current event instead of the session's true last one (the same default-frame trap that affects any window aggregate that relies on LAST_VALUE). Wrap the result in SELECT DISTINCT (or aggregate it afterward) to collapse the per-event output down to one row per session. Reach for FIRST_VALUE/LAST_VALUE instead of a plain MIN/MAX specifically when you need another column's value from the same row as the minimum or maximum, since MIN/MAX alone only give you the extreme value of the aggregated column itself, not the rest of that row.

Structured elaboration

• Window-function version: FIRST_VALUE/LAST_VALUE with PARTITION BY session_id ORDER BY event_time and the widened frame, then SELECT DISTINCT session_id, ... to fold the many event rows per session down to one output row per session.
• Why not just MIN(event_time)/MAX(event_time): MIN(event_time) tells you the earliest timestamp in the session, but nothing about which event_name happened at that timestamp; getting that co-located column otherwise requires a self-join or correlated subquery back to the original table. FIRST_VALUE(event_name) OVER (... ORDER BY event_time) gets both the extreme value and any other column from that same row in a single pass.
• Same idea applied to "each customer's first purchase date and the product from that order together": FIRST_VALUE(order_date) and FIRST_VALUE(product), both windowed the same way (PARTITION BY customer_id ORDER BY order_date), pull the date and the product from the same first order in one query, which a plain MIN(order_date) cannot do on its own.

Worked example

SELECT DISTINCT
  session_id,
  FIRST_VALUE(event_name) OVER (PARTITION BY session_id ORDER BY event_time
                                 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_event,
  FIRST_VALUE(event_time) OVER (PARTITION BY session_id ORDER BY event_time
                                 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_event_time,
  LAST_VALUE(event_name)  OVER (PARTITION BY session_id ORDER BY event_time
                                 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_event,
  LAST_VALUE(event_time)  OVER (PARTITION BY session_id ORDER BY event_time
                                 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_event_time
FROM session_events;

Executed against two sessions: session s1 has three events (page_view at 10:00, add_to_cart at 10:02, checkout at 10:05), giving first_event = page_view at 10:00 and last_event = checkout at 10:05. Session s2 has two events, both named page_view (at 11:00 and 11:01), giving first_event = last_event = page_view, but with distinct first_event_time = 11:00 and last_event_time = 11:01, confirming the query correctly distinguishes "same event name" from "same event time."

The first-purchase-plus-product variant, executed against orders(customer_id, order_date, product) with customer 1's earliest order on Jan 1 for a Widget (a later order on Jan 10 for a Gadget) and customer 2's single order on Feb 1 for a Gizmo, correctly returns (1, 2024-01-01, Widget) and (2, 2024-02-01, Gizmo).

Complexity

Getting first/last event per session needs each session's rows sorted by event_time first: O(n log n) without an index on (session_id, event_time), O(n) with one. Because the frame is UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING, the engine has to hold each session's full row set before it can emit LAST_VALUE for any row in that session, unlike a CURRENT ROW-bounded running total that can stream through a partition incrementally; a session with an unusually large number of events raises memory pressure and spill risk for that one partition specifically. The SELECT DISTINCT on top is an extra dedup pass over the (already one-row-per-original-event) output, cheap relative to the sort, but still a second full pass over the data, not free.

Trade-offs & pitfalls

• An alternative worth naming: ROW_NUMBER() OVER (PARTITION BY session_id ORDER BY event_time) = 1 (and a mirrored DESC version for the last row), then filter to that row directly. It is more verbose for one or two columns, but clearer and less repetitive than FIRST_VALUE/LAST_VALUE once you need many columns from the same first or last row, since a ROW_NUMBER filter lets you SELECT * instead of repeating the same OVER clause for every column.
• Ties on event_time need a documented tie-breaker (an event_id); without one, which specific tied row's other columns FIRST_VALUE/LAST_VALUE return is not guaranteed to be consistent across runs.
• SELECT DISTINCT over window-function output can hide a broken frame rather than surfacing it: if the frame is left at its buggy default, DISTINCT will simply return one row per original event instead of one row per session (no error, just a wrong-shaped result), so validate that the output row count actually equals the number of distinct sessions as a sanity check, rather than trusting that the query ran without errors.

Direct answer: Start the recursion at the leaf (the employee whose chain you want), climb to their manager one join at a time, and stop when either the manager chain runs out (reached the top), a depth cap is hit, or a manager id you've already visited on this path shows up again. A recursive common table expression (CTE), written with WITH RECURSIVE, has two parts: an anchor query that seeds the starting rows, and a recursive term that repeatedly joins the CTE back to the base table until nothing new is produced or a stopping condition fires.

Approach

Anchor at every employee; recursive term joins to that employee's manager, prepending the manager's name to build the path and appending the manager's id to a visited-id array to guard against cycles.

WITH RECURSIVE reporting_chain AS (
  -- anchor: every employee starts as their own chain of length 1
  SELECT
    employee_id AS start_id,
    employee_id,
    manager_id,
    name::VARCHAR AS path,
    1 AS depth,
    ARRAY[employee_id] AS visited
  FROM employees

  UNION ALL

  -- recursive term: climb one level to the manager, prepend their name
  SELECT
    rc.start_id,
    m.employee_id,
    m.manager_id,
    m.name || ' > ' || rc.path,
    rc.depth + 1,
    rc.visited || m.employee_id
  FROM reporting_chain rc
  JOIN employees m ON m.employee_id = rc.manager_id
  WHERE rc.depth < 10                       -- depth cap
    AND NOT (m.employee_id = ANY (rc.visited))  -- cycle guard
),
final_chain AS (
  SELECT start_id, path, depth,
    ROW_NUMBER() OVER (PARTITION BY start_id ORDER BY depth DESC) AS rn
  FROM reporting_chain
)
SELECT start_id AS employee_id, path AS reporting_path, depth
FROM final_chain
WHERE rn = 1
ORDER BY employee_id;

Key points

• UNION ALL, not UNION: the recursive term is expected to keep producing new (start_id, employee_id) pairs at increasing depth; UNION would force a distinctness check across every column on every iteration, which is both unnecessary (the visited-array guard already prevents true infinite loops) and expensive.
• The depth cap (rc.depth < 10) belongs in the recursive term's WHERE, not as a post-hoc LIMIT, because LIMIT on the final result doesn't stop the recursion itself from running arbitrarily deep first.
• ROW_NUMBER() ... ORDER BY depth DESC, filtered to rn = 1, picks each employee's longest (i.e., most complete) chain out of the intermediate partial chains the recursion necessarily also produces along the way.
• PostgreSQL requires the RECURSIVE keyword (WITH RECURSIVE); SQL Server's WITH does not use it at all, so this exact syntax is not portable as written across those two engines.

Worked example

Employees: (1, NULL, 'CEO'), (2, 1, 'VP'), (3, 2, 'Manager'), (4, 3, 'Employee').

Verified in PostgreSQL, the query returns:

Employee 4's path matches the target string exactly: CEO > VP > Manager > Employee.

graph TD
  CEO --> VP
  VP --> Manager
  Manager --> Employee

Complexity
Each recursive step is a join from the current frontier of rows back to employees on manager_id; with an index on employees(manager_id) (or the id used to join upward), each step costs proportional to the number of rows at that depth. Overall cost is bounded by (number of employees) x (average chain depth), since in a genuine org chart every employee contributes exactly one row per depth level of their own chain; the depth cap turns a potential unbounded cost into a hard ceiling regardless of how deep or malformed the underlying data is.

Edge cases

• Cycle in the data (e.g., a bad edit makes employee A report to employee B who reports back to A): verified by testing with (2,3,'Alice'), (3,2,'Bob'), (4,2,'Carl') as the sole rows. The recursion for employee 4 produces depth-1 through depth-3 rows and then correctly stops itself once climbing from manager 3 would revisit employee 2, which is already in the visited array; it never reaches the depth-10 cap and never loops.
• A chain longer than the depth cap: silently truncated at 10 levels; if that's a real risk in your org data, surface a flag on rows that hit the cap rather than letting a truncated chain look identical to a genuinely complete one.
• An employee with manager_id IS NULL (the CEO): the anchor row for such an employee is already their complete, correct one-row chain; the recursive term simply never matches for them since there's no manager row to join to.
• If the target engine lacks array types, substitute a delimiter-separated string for visited and check membership with a LIKE pattern (e.g., '|' || rc.visited || '|' LIKE '%|' || m.employee_id || '|%'); it's less type-safe than an array but portable to engines without array support.

Trade-offs & pitfalls

The same shape, sometimes named level or distance from root instead of depth, and sometimes carrying an extra manager_name column pulled straight off each recursive step rather than folded into a path string, shows up repeatedly as the standard org-chart interview pattern; the depth counter, cycle guard, and anchor-plus-recursive-term structure are the substance being tested, the exact column names are cosmetic. For a hierarchy that's queried often but changes rarely (an org chart isn't restructured every minute), consider materializing the flattened chain into a table refreshed on a schedule or on write, rather than recomputing this recursive CTE on every dashboard load.

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.