Question 1

Top-N per group with RANK() OVER (PARTITION BY) — how do you get top 2 per department without collapsing rows?

Accepted Answer

**Target Audience:** Beginners who have seen `GROUP BY` and want to return the best N rows of every group, and candidates preparing for the tie and performance follow-ups.
**Difficulty:** Medium
**Engines:** SQLite 3.45.0 (sql.js 1.10.2, the engine the runnable examples on this page use) and PostgreSQL 16.15, both run for this page. The window-function pattern is standard SQL and also works in MySQL 8.0+ and SQL Server.

> **How to read this doc:** Every idea is explained in plain language first

Question 2

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

Accepted Answer

**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

Question 3

What is a LATERAL join?

Accepted Answer

**Target Audience:** Developers comfortable with joins and subqueries who meet `LATERAL` in PostgreSQL code; candidates who need the precise definition and its uses.
**Difficulty:** Hard
**Engines:** PostgreSQL 16.15 (all LATERAL examples were run there), SQLite 3.45.0 via sql.js 1.10.2 for the runnable SQLite equivalent. MySQL and SQL Server facts are cited, not run.

> **How to read this doc:** Every idea is explained in plain language first, then tagged with `📌 Interview term:` so you also l

Question 4

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

Accepted Answer

**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

SQLDECODED.

Medium

Top-N per group with RANK() OVER (PARTITION BY) — how do you get top 2 per department without collapsing rows?

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

Hard

What is a LATERAL join?

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