mediumDSA

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?

`JOIN LATERAL` (or `CROSS APPLY` in SQL Server) lets the right-hand subquery reference columns from the left row and runs once per outer row. Perfect for “top-N per group” with an index on `(customer_id, order_date DESC)`. LATERAL = correlated subquery in FROM — runs once per outer row, can return many columns Outer Row customers c one row at a time c.id = ? LATERA

1.1k views

Understand the problem

Use CROSS/LEFT JOIN LATERAL as a per-row correlated subquery that can return multiple columns without aggregation.

lateral-jointop-npostgres

Attempt it yourself

Sketch your approach before reading the solution — that's what interviews test.

Nudge consolestandby

Stuck? Beam a request up — the console returns a conceptual nudge that guides your logic without spoiling the implementation.

Study the solution

JOIN LATERAL (or CROSS APPLY in SQL Server) lets the right-hand subquery reference columns from the left row and runs once per outer row. Perfect for “top-N per group” with an index on (customer_id, order_date DESC).

Solution ready — 2 min read

Classified // press E to declassify

Join the discussion

Discussion (0)

No responses yet. Be the first to share what you think.

Transmission complete // awaiting log

KEEP THE
STREAK ALIVE.

Dossier 19 of 70 decoded in the SQL track. One more won't hurt.

Back to track

Next up→

What is a covering index?

Continue