LATERAL JOIN — how do you fetch the top 3 orders per customer efficiently?

Q: LATERAL JOIN — how do you fetch the top 3 orders per customer efficiently?

**Target Audience:** Developers who know what LATERAL is and now need the classic top-N-per-group query to be fast; the applied interview version. **Difficulty:** Medium **Engines:** PostgreSQL 16.15 for the LATERAL queries, plans and the million-row timing test; SQLite 3.45.0 (sql.js 1.10.2, the engine the runnable example on this page uses) for the portable window-function version. > **How to read this doc:** Every idea is explained in plain language first, then tagged with `📌 Interview term:` so you also learn the exact words an interviewer expects. Every result and plan 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 member, show their three most expensive late fees."* One way: print every late fee ever charged, sort the whole pile by member and amount, and walk through it keeping the first three per member. That works, but the pile has a million s

The problem

Question presented to candidate: "For each customer, we want their three biggest orders, for a dashboard that shows a page of customers at a time. How would you write that in PostgreSQL so it stays fast when the orders table has millions of rows?"

What a strong answer should cover:

CROSS JOIN LATERAL (SELECT ... WHERE o.customer_id = c.id ORDER BY amount DESC, id DESC LIMIT 3): one small top-3 query per customer
The index that makes it fast: (customer_id, amount DESC, id DESC), so each inner query reads exactly three index entries (PostgreSQL shows Limit over an Index Only Scan with loops = number of customers)
A deterministic tie-breaker, and the alternative FETCH FIRST 3 ROWS WITH TIES (PostgreSQL 13+) when tied orders should all be shown
LEFT JOIN LATERAL ... ON true when customers without orders must still appear
The trade-off against ROW_NUMBER() OVER (PARTITION BY customer_id ...): the window version reads every order, LATERAL reads three per customer; which wins depends on rows per customer and how many customers you need
Portability: SQLite has no LATERAL, SQL Server uses CROSS APPLY with TOP (3), MySQL supports LATERAL from 8.0.14

Clarifying questions expected:

"Biggest" by amount or "latest" by date, and what happens with ties at third place?
Should customers with no orders appear, and do we need all customers or a page of them?

Code / implementation expected: Yes — the LATERAL query with the supporting index, ideally with the plan and the window-function alternative.

Schema used by the examples:

lateral-join
top-n
postgres

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 know what LATERAL is and now need the classic top-N-per-group query to be fast; the applied interview version. Difficulty: Medium Engines: PostgreSQL 16.15 for the LATERAL queries, plans and the million-row timing test; SQLite 3.45.0 (sql.js 1.10.2, the engine the runnable example on this page uses) for the portable window-function version.

How to read this doc: Every idea is explained in plain language first, then tagged with 📌 Interview term: so you also learn the exact words an interviewer expects. Every result and plan 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 member, show their three most expensive late fees." One way: print every late fee ever charged, sort the whole pile by member and amou

The solution is hidden so you can try first

12 min read · 12 sections

Run the code

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

Top 3 orders per customer with CROSS JOIN LATERAL

SELECT c.name, t.order_id, t.amount, t.ordered_at
FROM customers AS c
CROSS JOIN LATERAL (
  SELECT o.id AS order_id, o.amount, o.ordered_at
  FROM orders AS o
  WHERE o.customer_id = c.id
  ORDER BY o.amount DESC, o.id DESC   -- id breaks ties
  LIMIT 3
) AS t
ORDER BY c.name, t.amount DESC, t.order_id DESC;

The plan: Limit over an Index Only Scan, once per customer

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

SQLSame result without LATERAL (runs in SQLite): ROW_NUMBER per customer

LEFT JOIN LATERAL with FETCH FIRST 3 ROWS WITH TIES: keep ties and customers with no orders

SELECT c.name, t.order_id, t.amount
FROM customers AS c
LEFT JOIN LATERAL (
  SELECT o.id AS order_id, o.amount
  FROM orders AS o
  WHERE o.customer_id = c.id
  ORDER BY o.amount DESC
  FETCH FIRST 3 ROWS WITH TIES
) AS t ON true
ORDER BY c.name, t.amount DESC, t.order_id;

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.