Advanced SQL: Window Functions, CTEs, and Subqueries Questions

One deterministic row per customer
Use ROW_NUMBER(), which assigns 1 to a single row after you specify a full tie-break order. ROW_NUMBER() is a window function, meaning it ranks rows inside each customer group without collapsing them.

WITH ranked AS (
  SELECT
    o.*,
    ROW_NUMBER() OVER (
      PARTITION BY customer_id
      ORDER BY order_ts DESC, order_id DESC
    ) AS rn
  FROM orders o
)
SELECT *
FROM ranked
WHERE rn = 1;

The timestamp decides recency, and order_id DESC makes ties deterministic.

If business later wants every row tied for the latest timestamp
Switch to RANK() and filter on 1.

WITH ranked AS (
  SELECT
    o.*,
    RANK() OVER (
      PARTITION BY customer_id
      ORDER BY order_ts DESC
    ) AS rnk
  FROM orders o
)
SELECT *
FROM ranked
WHERE rnk = 1;

Example
If customer 7 has rows at 10:00, 10:00, and 09:30, the first query returns one row. The second returns both 10:00 rows.

Direct answer: Both the ROW_NUMBER()-and-filter approach and a LATERAL join with LIMIT compute the same top-3-orders-per-user result, but they get there differently: ROW_NUMBER() ranks every row in the table in one pass and then discards everything past rank 3, while LATERAL re-runs a small, independently sorted LIMIT 3 subquery once per user. On a warehouse with millions of users, that difference matters: ROW_NUMBER() requires one global sort (or a hash-based partitioned sort) over the whole table, while LATERAL can exploit an index on (user_id, amount) to answer each user's top-3 without ever sorting the full table, at the cost of effectively running millions of small queries instead of one big one.

Structured elaboration

Approach 1: ROW_NUMBER() filtered in an outer query.

SELECT order_id, user_id, amount, order_date FROM (
  SELECT order_id, user_id, amount, order_date,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY amount DESC, order_date DESC) AS rn
  FROM orders
) t
WHERE rn <= 3;

Approach 2: LATERAL join with LIMIT.

SELECT u.user_id, o.order_id, o.amount, o.order_date
FROM (SELECT DISTINCT user_id FROM orders) u
CROSS JOIN LATERAL (
  SELECT order_id, amount, order_date
  FROM orders o
  WHERE o.user_id = u.user_id
  ORDER BY amount DESC, order_date DESC
  LIMIT 3
) o;

Worked example (executed in DuckDB). User 1 has 4 orders (amounts 50, 80, 20, 80, with a tie at 80 broken by order_date DESC); user 2 has 1 order.

-- ROW_NUMBER, rn <= 3:
user_id | order_id | amount | order_date
1       | 4        | 80     | 2025-01-04
1       | 2        | 80     | 2025-01-02
1       | 1        | 50     | 2025-01-01
2       | 5        | 10     | 2025-01-01

The LATERAL version returns the identical set of 4 rows. One thing the execution surfaced worth naming explicitly: the LATERAL subquery's internal ORDER BY amount DESC, order_date DESC correctly picks orders 4 and 2 as the top two, but if the OUTER query's final ORDER BY doesn't repeat the full tiebreak key (e.g. it only sorts by amount DESC and drops order_date DESC), the two tied rows can come back in a different relative order than the ROW_NUMBER() version, purely because nothing downstream of the LATERAL join is enforcing that tiebreak anymore. This is the same underlying lesson as tie-break determinism elsewhere in this topic: a per-group ranking is only as deterministic as the LAST place its ORDER BY is actually enforced, and a join can silently lose that ordering if the outer query doesn't restate it.

Readability and performance trade-offs at millions of users

Neither is universally faster: a columnar warehouse without per-row indexes gains nothing from LATERAL and pays the overhead of a correlated execution per user instead; a row store with a good composite index can make LATERAL meaningfully cheaper than sorting every row in the table just to discard most of it.

When N varies per group instead of being a fixed constant. Join a per-user n value in before filtering, and compare rn <= n instead of rn <= 3:

SELECT t.order_id, t.user_id, t.amount, t.order_date FROM (
  SELECT o.order_id, o.user_id, o.amount, o.order_date,
    ROW_NUMBER() OVER (PARTITION BY o.user_id ORDER BY o.amount DESC, o.order_date DESC) AS rn
  FROM orders o
) t
JOIN user_n un ON un.user_id = t.user_id
WHERE t.rn <= un.n;

Run against a user_n table giving user 1 an N of 2 and user 2 an N of 1, this correctly returns only the top 2 for user 1 (orders 4 and 2) and the top 1 for user 2 (order 5), confirming the join-based cutoff works exactly like a per-row constant would. The LATERAL version's equivalent change is simpler still: replace the literal LIMIT 3 with LIMIT (SELECT n FROM user_n WHERE user_n.user_id = u.user_id), since LATERAL's subquery can already reference the outer row.

Trade-offs & pitfalls

• ROW_NUMBER() with a variable N needs an extra join and a comparison against a column instead of a literal; LATERAL needs a scalar subquery inside LIMIT, which not every engine allows (some require LIMIT to be a literal or a simple parameter, not an arbitrary subquery).
• Always finish the ORDER BY chain with a column that breaks any remaining tie deterministically (an order id, as used above via order_date DESC then implicitly stable ordering); otherwise which of two tied top-3 candidates gets included, or which order they print in, is not guaranteed to be stable across runs, exactly as demonstrated in the executed example above.
• EXPLAIN the specific engine and index combination before choosing; the trade-off table above is directional reasoning about access patterns, not a promise that one form is always faster, since it depends heavily on whether a supporting index actually exists and whether the optimizer chooses to use it for a LATERAL correlated subquery.

Direct answer: PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY col) is the exact, standard way to compute a median in engines that support ordered-set aggregates (Postgres, Snowflake, BigQuery, DuckDB): it sorts the group and interpolates between the two middle values. Engines without it (MySQL, SQLite, older warehouses) need a manual ROW_NUMBER() workaround that ranks each group and averages the middle row (even count) or takes the single middle row (odd count). Switch to an approximate, sketch-based quantile function once the exact sort-per-group computation becomes the bottleneck at scale, and tell the stakeholder plainly: "this number is very close but not exact, and it will stay fast as the data grows."

Structured elaboration

Approach 1: exact, ordered-set aggregate

PERCENTILE_CONT is an ordered-set aggregate function, not a plain window function. It always needs WITHIN GROUP (ORDER BY ...) and a GROUP BY (or no GROUP BY at all for one global median). PostgreSQL's documentation is explicit that ordered-set aggregates cannot be used as window functions with OVER (...); DuckDB rejects the same syntax outright. On PostgreSQL and DuckDB specifically, if you need a per-partition median attached to every row (window-style), you compute the aggregate in a subquery or CTE (common table expression, a named, reusable subquery introduced with WITH) and join it back, since neither engine lets you bolt OVER (PARTITION BY ...) directly onto PERCENTILE_CONT. That restriction is not universal: Snowflake and Redshift both document PERCENTILE_CONT(...) WITHIN GROUP (ORDER BY ...) OVER (PARTITION BY ...) as valid windowed syntax, so on those two engines the per-partition median can be a single expression with no join-back step.

Approach 2: manual ROW_NUMBER() workaround (portable, works anywhere)

Rank each row within its group by the target column, count the group size, and average the row(s) sitting at the middle rank(s):

WITH numbered AS (
  SELECT
    category_id,
    amount,
    ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY amount) AS rn,
    COUNT(amount) OVER (PARTITION BY category_id) AS cnt
  FROM transactions
  WHERE amount IS NOT NULL
)
SELECT category_id, AVG(amount) AS median_amount
FROM numbered
WHERE rn IN (FLOOR((cnt + 1) / 2.0), CEILING((cnt + 1) / 2.0))
GROUP BY category_id;

For an odd group size the two FLOOR/CEILING picks land on the same rank, so AVG of one value is just that value; for an even group size they land on the two middle ranks and AVG interpolates the way PERCENTILE_CONT does. NTILE(2) is sometimes mentioned in the same breath as this workaround, but it is a coarser tool: it splits a group into equal-sized buckets and tells you which bucket a row falls in, it does not interpolate a boundary value, so it is the right primitive for quartile/decile bucketing, not a precise substitute for a point-estimate median.

Approach 3: approximate, sketch-based quantile estimation

Once a partition is large enough that sorting it (the O(n log n) step both approaches above rely on) means spilling to disk or holding the whole group in memory, switch to a probabilistic sketch such as t-digest, a compact, mergeable summary structure that approximates quantiles instead of computing them exactly. These structures process the data in one streaming pass, keep a small, bounded amount of state regardless of how many rows feed into it, and answer a quantile query with a small, known error bound instead of an exact sort. Every major analytical warehouse ships a built-in function for this: DuckDB's APPROX_QUANTILE, Snowflake's APPROX_PERCENTILE (built on a t-digest variant), BigQuery's APPROX_QUANTILES (returns an array of boundary values), and Redshift's APPROXIMATE PERCENTILE_DISC.

When to switch, and how to say it to a stakeholder

Switch when the exact computation is measurably the bottleneck: a group so large that the sort spills to disk, a high-cardinality GROUP BY key producing thousands of small sorts, or a dashboard that needs to stay responsive as the underlying table grows unbounded. Do not switch pre-emptively on a small or medium table just because "approximate" sounds more scalable; exactness is free until it isn't. When a stakeholder asks for "the median" and you plan to serve an approximate one, say what changes in plain terms: the number will be extremely close to the true median (typically within a fraction of a percent for standard sketch sizes) and it will stay fast no matter how much data accumulates, versus an exact number that is guaranteed correct but gets slower, and eventually a background batch job, as volume grows. If the number feeds a regulated or audited report, keep it exact; if it feeds a live operational dashboard, approximate is almost always the right trade.

Extension: exact-vs-approximate distinct counts

The same tension shows up one level over, on COUNT(DISTINCT user_id) for a daily active-users metric. An exact distinct count forces the engine to track every value it has already seen (a hash set or a sort-and-dedupe pass), so memory and compute scale with the number of distinct values, not just the row count, and a single query with several COUNT(DISTINCT ...) metrics can be especially expensive because each one needs its own tracking structure. HyperLogLog-style sketches solve it the same way t-digest solves quantiles: hash each value, keep a small fixed-size register array (kilobytes, not megabytes, regardless of whether the true distinct count is a thousand or a billion), and estimate the cardinality from the distribution of leading zero-bits observed. The error shrinks as the sketch's register count grows, and standard sizes comfortably keep the relative error to a low single-digit percentage, which is normally far tighter than a stakeholder needs for a trend line. DuckDB exposes this as APPROX_COUNT_DISTINCT; BigQuery, Snowflake, and Redshift ship equivalents (BigQuery additionally exposes the raw HLL_COUNT.* sketch functions so you can store and merge partial sketches across days instead of recomputing from scratch).

Worked example

Ran against a real transactions(category_id, amount) table in DuckDB, category_id = '1' has an odd count of five rows (10, 20, 30, 40, 100), category_id = '2' has an even count of four rows (5, 15, 25, 35):

-- Approach 1: exact, ordered-set aggregate
SELECT category_id, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount) AS median_amount
FROM transactions GROUP BY category_id;

Category 1's median is the literal middle value (30). Category 2 has no single middle value, so PERCENTILE_CONT interpolates between the two middle values 15 and 25, giving (15+25)/2 = 20. The manual ROW_NUMBER() query from Approach 2, run against the same table, returned identical results (30 and 20), confirming the two exact methods agree. APPROX_QUANTILE(amount, 0.5) also returned 30 and 20 on this table, because the demo dataset is far too small for a sketch's approximation error to show up. The whole point of switching to a sketch only pays off at the row counts where an exact sort would actually hurt, which a nine-row demo table cannot honestly simulate; the sketch's real behavior is a bounded-error, bounded-memory summary, not a different answer on small data.

For the distinct-count extension, an active_users(event_date, user_id) table with 3 distinct users on day one and 4 distinct users on day two returned identical results from COUNT(DISTINCT user_id) and APPROX_COUNT_DISTINCT(user_id) for the same reason: at single-digit cardinality there is nothing for a sketch to approximate away.

Key points

• PERCENTILE_CONT is an ordered-set aggregate: exact, needs WITHIN GROUP and GROUP BY, and (verified against PostgreSQL's own documentation and by direct execution) cannot take an OVER (PARTITION BY ...) clause the way a true window function can.
• PERCENTILE_CONT silently drops NULL values from the ordering before computing the median (verified: a group of 10, 20, NULL, 30 returned a median of 20, the same as if the NULL were never there).
• NTILE buckets rows into N groups; it is a bucketing tool, not an interpolating median calculator.

Complexity

Both exact approaches require a full sort of each group: O(n log n) per group, and the whole group must be materialized before the middle value(s) can be identified, so memory scales with the largest group's size. Sketch-based approximate methods process each row once, O(n) total, using a fixed, small amount of memory per sketch regardless of group size, which is exactly what makes them viable when the exact sort would spill to disk.

Edge cases

• Empty group: both PERCENTILE_CONT and the ROW_NUMBER() approach return NULL (no rows to rank).
• NULL values in the target column: excluded from the ordering before the median is computed; make sure cnt in the manual approach is a NULL-excluding count (COUNT(amount), not COUNT(*)) or the rank-matching math drifts.
• Ties at the middle rank(s) are handled correctly by both exact approaches since they operate on sorted position, not on distinct values.

Trade-offs & pitfalls

The trap most candidates miss is reaching for NTILE as if it were a median shortcut; it answers a different question (which bucket) and does not interpolate. The trap on the approximate side is presenting a sketch-based number as if it were exact without saying so: a dashboard KPI silently switching from exact to approximate after a "performance fix" is the kind of change that erodes stakeholder trust in the numbers, even when the approximation itself is well within an acceptable error band. State the switch and its error characteristics explicitly rather than letting it hide inside a query rewrite.

Direct answer: Pick the tool by matching its physical behavior to what the KPI (key performance indicator) actually needs: a common table expression (CTE, a named subquery written with WITH ... AS (...)) for a one-off analysis you'll run once and throw away, a temp or staging table when the calculation is expensive but the reuse window is short (a single session, a single ETL run, meaning extract-transform-load), and a materialized view (a query whose result is physically stored on disk and refreshed on a schedule or trigger, rather than recomputed on every read) once the same expensive logic is read repeatedly by multiple dashboards and can tolerate being slightly stale. Near-real-time dashboards are the one case where none of the "precompute it" options fit cleanly: they usually need a lean, indexable live query instead, or an incrementally-updated summary table rather than a full materialization.

Structured elaboration

Recommendations by scenario:

• One-off ad hoc analysis: a CTE (or a plain subquery). There is no second reader to amortize setup cost against, so the fastest path to an answer wins over any persistence machinery.
• Repeatedly-used expensive calculation: a materialized view (or, where the engine lacks native materialized views, a scheduled job that populates a plain table). The cost of computing it is paid once per refresh instead of once per dashboard load, and it becomes indexable, which a CTE never is.
• Near-real-time dashboard: avoid full materialization; either query the base tables directly with supporting indexes so the planner can push filters down, or maintain an incrementally-updated summary table (updated on write, not recomputed wholesale) if the underlying computation is too heavy to run live on every page load.

When does a CTE pipeline graduate to a materialized view? Three independent signals, any one of which is usually enough on its own: (1) reuse -- the same CTE logic is being copy-pasted into a second, third, or fourth query or dashboard, which is a sign the definition should live in one place instead of many; (2) performance -- the CTE's underlying computation starts showing up as the dominant cost in an EXPLAIN plan across multiple callers, so the aggregate cost of recomputing it everywhere now exceeds the cost of maintaining a refreshed copy; (3) governance -- different query authors start writing slightly different filters around a copy-pasted CTE, so the "same" KPI silently drifts between dashboards. Any of these is the point to promote the logic into a materialized view (or persisted table) with one refresh schedule and one definition that every consumer reads.

Worked example

A KPI like "monthly active accounts" (distinct accounts with at least one qualifying event in the trailing 30 days) is a good stand-in: it is expensive on a large events table because it needs a distinct count over a rolling window, and it is exactly the kind of metric multiple dashboards want to show.

-- (a) one-off exploration: CTE, thrown away after this single query
WITH active_accounts AS (
  SELECT DISTINCT account_id
  FROM events
  WHERE event_time >= CURRENT_DATE - INTERVAL '30 days'
)
SELECT COUNT(*) AS mau FROM active_accounts;

-- (b) repeatedly-used, refresh-tolerant: materialized view, indexed, refreshed nightly
CREATE MATERIALIZED VIEW mv_monthly_active_accounts AS
SELECT account_id, MAX(event_time) AS last_active_at
FROM events
WHERE event_time >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY account_id;

CREATE INDEX idx_mv_maa_account ON mv_monthly_active_accounts(account_id);
-- refreshed on a schedule, e.g. REFRESH MATERIALIZED VIEW mv_monthly_active_accounts;

-- (c) near-real-time: query live, relying on an index on events(account_id, event_time)
-- rather than pre-aggregating, so results are current to the second
SELECT COUNT(DISTINCT account_id) AS mau_live
FROM events
WHERE event_time >= NOW() - INTERVAL '30 days';

ML (machine learning) feature pipeline framing: this exact decision tree also applies when the "dashboard" is actually a model training job reading a feature. A feature explored once in a notebook is a CTE; a feature reused across many training runs (and needing point-in-time correctness, i.e. only using data available as of each label's timestamp) is exactly the "repeatedly-used expensive calculation" case, and belongs in a materialized feature table (or a feature store) that is refreshed on a known cadence, not recomputed inline in every training query. The freshness criterion becomes sharper here: a stale feature table used for training is often fine, but the same staleness at serving/inference time can silently create training-serving skew, so the acceptable staleness window has to be decided per use case, not assumed.

Spark SQL / Catalyst optimizer angle: the reuse math is different in Spark. Spark's Catalyst optimizer treats a WITH clause referenced multiple times by inlining and re-planning the underlying logical plan at each reference site by default, rather than computing it once and sharing the result the way a materialized view would. A CTE joined against three times in one Spark SQL query can trigger the same expensive computation three separate times unless you explicitly force sharing (persisting the intermediate DataFrame with .cache()/.persist(), or writing it out and reading it back). This makes the "graduate to materialization" decision arrive sooner in Spark than in a single-node warehouse: a CTE reused even twice within one query is worth checking for repeated work, not just a CTE reused across many separate queries.

Trade-offs & pitfalls

The common wrong turn is reaching for a materialized view purely because a query is slow, without checking whether the consumer can actually tolerate the staleness that comes with it: a materialized view refreshed hourly is the wrong answer for a dashboard that promises "as of right now." Conversely, leaving an expensive, frequently-reused CTE unmaterialized "for simplicity" quietly multiplies its cost by however many dashboards call it, since nothing about a CTE shares work across separate queries. Temp/staging tables sit in between and are easy to over- or under-use: they are the right tool inside a single multi-step job (build once, query several times, then discard), but reaching for one to serve a dashboard means re-inventing a materialized view's refresh logic by hand, usually worse. Always confirm the actual freshness requirement with the stakeholder before choosing: "near-real-time" and "updated every 15 minutes" are very different engineering problems that get conflated in casual requirements language.

Window functions are evaluated after WHERE filters rows, so a window function's result doesn't exist yet at the point WHERE runs; SQL simply doesn't allow referencing one there (or in GROUP BY, for the same reason). To filter on a cumulative spend computed with a window function, compute it first in a CTE (common table expression, a named WITH block) or subquery, then filter the outer query against that already-computed column.

Approach

WITH customer_year_spend AS (
  SELECT
    customer_id,
    order_id,
    order_date,
    amount,
    SUM(amount) OVER (
      PARTITION BY customer_id, EXTRACT(YEAR FROM order_date)
      ORDER BY order_date, order_id
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_spend
  FROM sales
)
SELECT DISTINCT customer_id
FROM customer_year_spend
WHERE cumulative_spend > 1000;

Key points

• The window function runs inside the CTE, over the full row set; the outer query's WHERE filters the CTE's already-materialized output, which is a normal column at that point, not a window function call.
• The explicit ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW frame matters: without it, the default frame for an ORDER BY-bearing window is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which groups every row sharing the same ORDER BY value (here, the same order_date) into one combined total for all of them, not a true row-by-row running sum.
• Because amount is non-negative, the cumulative sum only ever increases through the year, so "the running total ever exceeds 1000" and "the year-end total exceeds 1000" pick out the exact same set of customers; DISTINCT customer_id over any row that clears the threshold is equivalent to filtering on the final total.

Why ROWS matters here: the RANGE default-frame trap

Two orders for the same customer on the same order_date (200 on 2024-03-10 and 150 on 2024-03-10, following an earlier 100 on 2024-01-05) expose the bug directly. With the default RANGE frame, both same-day rows are treated as peers and both get the combined total of all peers as their cumulative value (450 for both), instead of the running total accumulating row by row (300 after the first of the two, 450 after the second). ROWS fixes this because it counts physical rows up to the current one, not "all rows with the same ORDER BY value."

(Verified by executing both the ROWS-framed and RANGE-framed versions of this window against the same data in SQLite 3.51; the RANGE result matches the buggy pattern exactly as shown.)

Complexity

Computing the window function is one ordered pass per partition: O(n log n) if the engine has to sort by (customer_id, order_date, order_id), or close to O(n) if an existing index already provides that order. The outer WHERE/DISTINCT is a single O(n) filter and de-duplication pass over the CTE's output. This is the same total cost as computing a plain GROUP BY/HAVING total would be; the window function version costs more only in the sense that it materializes a row per order instead of collapsing straight to one row per customer.

Edge cases

• Same-order_date orders: handled correctly only with the explicit ROWS frame, as shown above; this is the single most common way this pattern silently breaks.
• Negative amounts (refunds): if amount can go negative, the cumulative sum is no longer monotonically increasing, and "any row exceeds the threshold" is no longer equivalent to "the year-end total exceeds the threshold"; in that case you must filter on the maximum cumulative value per customer-year, not just any exceeding row, or decide explicitly which semantics the business actually wants.
• Customers with zero orders in the year: they never appear in sales at all for that year, so they're correctly absent from the result without any extra handling.

Trade-offs and pitfalls

If the business question is genuinely just "whose total for the year exceeds the threshold," a plain GROUP BY customer_id HAVING SUM(amount) > 1000 computes the same customer set with less work: one aggregation pass, no window function, no CTE wrapper, and no risk of the RANGE/ROWS frame trap at all. The window-function-plus-CTE form earns its keep when the question is really about the running value, not just the total: for example, flagging the exact order at which a customer crossed a loyalty threshold, not just whether they crossed it by year-end. Reach for GROUP BY/HAVING when you only need the final number, and reach for the windowed CTE when the row-by-row trajectory itself is part of what's being asked.