hardSystem Design

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?

Small number of groups + selective index → `LATERAL` wins (N index scans). Many groups or no supporting index → `ROW_NUMBER() OVER (PARTITION BY)` single scan+sort wins. Correlated `IN` is limited to one column and rarely optimal. Top-N per group: LATERAL vs ROW_NUMBER() window — choose by index & cardinality LATERAL JOIN index on (customer_id, date) Index Scan per group fast for few groups needs outer rows small <rect x="290" y="35" width="240" height=

221 views

Understand the problem

Compare execution strategies, index requirements, and planner choices for grouped top-N queries at scale.

lateral-joinquery-planningperformance

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

Small number of groups + selective index → LATERAL wins (N index scans). Many groups or no supporting index → ROW_NUMBER() OVER (PARTITION BY) single scan+sort wins. Correlated IN is limited to one column and rarely optimal.

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 68 of 70 decoded in the SQL track. One more won't hurt.

Back to track

Next up→

What is the difference between a clustered and non-clustered index?

Continue