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

What is the difference between ROW_NUMBER, RANK, and DENSE_RANK?

Accepted Answer

**Target Audience:** Beginners who have just met window functions and want the three ranking functions explained with real numbers, plus candidates who want the exact definitions.
**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.

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

Question 3

What are window functions?

Accepted Answer

**Target Audience:** Beginners who know `GROUP BY` and want to understand the `OVER (...)` syntax from zero, plus candidates who want the precise vocabulary (partition, frame, peers).
**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. MySQL facts are cited from its documentation.

> **How to read this doc:** Every idea is explained in plain language first, then tagged with `📌 Intervie

Question 4

RANK() vs DENSE_RANK() vs ROW_NUMBER() — when does the gap matter and how do you choose?

Accepted Answer

**Target Audience:** Developers who know what the three ranking functions return and now need to choose one for a real task (top N, Nth highest, dedup), plus candidates preparing for the follow-ups.
**Difficulty:** Hard
**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. SQL Server and PostgreSQL version facts are cited.

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

SQLDECODED.

Medium

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

What is the difference between ROW_NUMBER, RANK, and DENSE_RANK?

What are window functions?

Hard

RANK() vs DENSE_RANK() vs ROW_NUMBER() — when does the gap matter and how do you choose?