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

How do you find the Nth highest salary?

Accepted Answer

**Target Audience:** Beginners meeting ranking queries for the first time, and candidates who want the tie, NULL and empty-result details interviewers use to separate answers.
**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 and SQL Server behaviour is cited from their manuals.

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

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

Question 7

pgvector cosine similarity index in 2025-26 — HNSW vs IVFFlat for semantic search at scale?

Accepted Answer

**Target Audience:** Developers adding semantic search to PostgreSQL, and candidates preparing for backend or ML-infrastructure interviews
**Difficulty:** Hard
**Engines:** PostgreSQL with the pgvector extension. pgvector is not installed on the machine that verified this page, so the index behaviour is explained with plain-SQL simulations that ran on sql.js 1.10.2 (SQLite) and PostgreSQL 16.15; pgvector SQL is marked "not run here" and its facts are cited from the [pgvector README](https://gith

Question 8

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