LATERAL vs Window Function vs Correlated Subquery for top-N per group — which scales when?

Q: LATERAL vs Window Function vs Correlated Subquery for top-N per group — which scales when?

**Target Audience:** Developers who can already write a top-N-per-group query and now need to choose the version that stays fast at scale; candidates preparing for the "which one would you ship" follow-up. **Difficulty:** Hard **Engines:** PostgreSQL 16.15 (plans and timings) and SQLite 3.45.0 through sql.js 1.10.2 (the runnable examples on this page). Notes on MySQL and SQL Server syntax are cited from their documentation. > **How to read this doc:** Ideas come first in plain language, then a `📌 Interview term:` callout gives the exact vocabulary an interviewer expects. Every result, plan and timing below was produced by actually running it; the **Verified** section quotes the example output in full. ## 1. Why This Even Matters — A Story First A librarian is asked: "for each of our 5 branches, which 2 books were returned most recently?" She has one giant returns ledger with 100,000 lines, sorted by branch and then by date, newest first. **Plan A:** start at line 1 and read the wh

The problem

Question presented to candidate: "You need the 3 most recent orders for every customer. You could write it with ROW_NUMBER() OVER (PARTITION BY ...), with a LATERAL join, or with a correlated subquery. They all return the same rows, so which one would you ship, and how does the answer change when there are 100 customers versus 200,000 customers on the same 1 million orders?"

What a strong answer should cover:

All three return the same rows when they use the same order and tiebreaker; the difference is how much data each one reads
Window function: reads every row of the table once (sorted, or in index order), cost grows with the total number of rows, not with N; PostgreSQL 15+ adds a Run Condition but still reads all rows
LATERAL (CROSS JOIN LATERAL / SQL Server CROSS APPLY): one index seek plus N rows per group, cost grows with groups x N; needs an index on (group_key, sort_key) and a cheap list of groups
Correlated subquery: IN (... LIMIT N) runs once per outer row (not per group), and the COUNT(*) form is quadratic inside each group; it is the portable fallback, rarely the fast one
The decision rule: few groups with many rows each and a matching index means LATERAL; many small groups, no index, or "rank everything anyway" means the window; prove it with EXPLAIN ANALYZE (loops and rows)

Clarifying questions expected:

How many groups are there, and how many rows per group (the ratio decides it)?
Is there an index on (customer_id, ordered_at DESC), and is there a separate customers table to drive the loop?
Do ties need to be handled (exactly N rows, or every row tied at the cut)?

Code / implementation expected: Yes — the same top-N query written all three ways, plus the plans that explain why one reads 10 rows and another reads 100,000.

Schema used by the examples:

lateral-join
query-planning
performance

Try it yourself

Sketch your approach before you read the solution. That is what the interview tests.

Stuck? Get a hint.

A short nudge toward the approach. It will not give the answer away.

Solution

Target Audience: Developers who can already write a top-N-per-group query and now need to choose the version that stays fast at scale; candidates preparing for the "which one would you ship" follow-up. Difficulty: Hard Engines: PostgreSQL 16.15 (plans and timings) and SQLite 3.45.0 through sql.js 1.10.2 (the runnable examples on this page). Notes on MySQL and SQL Server syntax are cited from their documentation.

How to read this doc: Ideas come first in plain language, then a 📌 Interview term: callout gives the exact vocabulary an interviewer expects. Every result, plan and timing below was produced by actually running it; the Verified section quotes the example output in full.

1. Why This Even Matters — A Story First

A librarian is asked: "for each of our 5 branches, which 2 books were returned most recently?" She has one giant returns ledger with 100,000

The solution is hidden so you can try first

10 min read · 10 sections

Run the code

Edit the query and run it against the tables for this question.

SQLWindow function: ROW_NUMBER() per customer, keep rn <= 2

SQLCorrelated subquery: keep an order if it is in that customer top 2 (IN ... LIMIT)

PostgreSQL: LATERAL on 100,000 orders reads 2 index entries per customer

-- 100,000 generated orders: 20,000 for each of the 5 customers
CREATE TABLE big_orders AS
SELECT g AS id, (g % 5) + 1 AS customer_id,
       DATE '2024-01-01' + (g % 365) AS ordered_at
FROM generate_series(1, 100000) AS g;
CREATE INDEX big_orders_recent ON big_orders (customer_id, ordered_at DESC, id DESC);
VACUUM ANALYZE big_orders;

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT c.name, t.id, t.ordered_at
FROM customers c
CROSS JOIN LATERAL (
  SELECT o.id, o.ordered_at
  FROM big_orders o
  WHERE o.customer_id = c.id
  ORDER BY o.ordered_at DESC, o.id DESC
  LIMIT 2
) t;

PostgreSQL: the window version of the same query reads all 100,000 entries

-- the same 100,000 generated orders
CREATE TABLE big_orders AS
SELECT g AS id, (g % 5) + 1 AS customer_id,
       DATE '2024-01-01' + (g % 365) AS ordered_at
FROM generate_series(1, 100000) AS g;
CREATE INDEX big_orders_recent ON big_orders (customer_id, ordered_at DESC, id DESC);
VACUUM ANALYZE big_orders;

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT customer_id, id, ordered_at
FROM (
  SELECT o.customer_id, o.id, o.ordered_at,
         ROW_NUMBER() OVER (PARTITION BY o.customer_id
                            ORDER BY o.ordered_at DESC, o.id DESC) AS rn
  FROM big_orders o
) ranked
WHERE rn <= 2;

Discussion

Other approaches, follow-ups and how it went in real interviews.

Sign in to join the discussion.

No comments yet. Share how you would answer it.

The solution is hidden so you can try first

Keep your momentum going.