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

How do you calculate year-over-year or month-over-month growth in SQL?

Accepted Answer

**Target Audience:** Beginners who can write `GROUP BY` and want to compare one period with the one before it, and candidates preparing for analytics interviews.
**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. `LAG()` is standard SQL and also exists in MySQL 8.0+ and SQL Server; the date functions differ per engine and are named where used.

> **How to read this doc:** Every idea is

Question 3

How do you calculate a running total or moving average?

Accepted Answer

**Target Audience:** Beginners who need a cumulative sum or a smoothed trend line in SQL, plus candidates who want to explain frames (`ROWS`, `RANGE`, `GROUPS`) precisely.
**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. Version facts are cited.

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

Question 4

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 5

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

Question 6

How do you solve gaps-and-islands problems in SQL?

Accepted Answer

**Target Audience:** Learners who know `ROW_NUMBER()` and `LAG()` and want to apply them to runs and holes in data, and candidates preparing for one of the classic hard SQL interview puzzles.
**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. The window functions are standard SQL; the date arithmetic is engine-specific and is shown for both.

> **How to read this doc:** Every idea is exp

SQLDECODED.

Medium