Question 1

How do you find duplicate rows in a table?

Accepted Answer

**Target Audience:** Beginners who know `SELECT` and want the standard duplicate-finding pattern, and candidates who want the details interviewers probe (NULLs, case, collation).
**Difficulty:** Easy
**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 behaviour is cited from its manual.

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

Question 2

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 3

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 4

What do the LAG and LEAD window functions do?

Accepted Answer

**Target Audience:** Beginners who want to compare a row with the row before or after it, plus candidates who want the arguments, the engine differences and the 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. SQL Server facts are cited from Microsoft documentation.

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

Question 5

Filtered vector search with pgvector — how do you combine WHERE category = 'X' with ORDER BY embedding <=> $1?

Accepted Answer

**Target Audience:** Developers running semantic search on PostgreSQL with pgvector, and candidates preparing for backend interviews
**Difficulty:** Medium
**Engines:** PostgreSQL with pgvector. pgvector is not installed on the verification machine, so the behaviour is shown with plain-SQL simulations that ran on sql.js 1.10.2 (SQLite) and a PostgreSQL 16.15 plan; pgvector SQL is marked "not run here" and its settings are cited from the [pgvector README](https://github.com/pgvector/pgvector).

>

Question 6

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 7

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 8

How do you delete duplicate rows but keep one?

Accepted Answer

**Target Audience:** Developers who can find duplicates and now need to remove them without losing data, and candidates preparing for the classic "delete duplicates, keep one" question.
**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 forms are cited, not run.

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

Question 9

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 10

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

SQLDECODED.

Easy

How do you find duplicate rows in a table?

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?

What do the LAG and LEAD window functions do?

Filtered vector search with pgvector — how do you combine WHERE category = 'X' with ORDER BY embedding <=> $1?

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

How do you calculate a running total or moving average?

How do you delete duplicate rows but keep one?

How do you find the Nth highest salary?

Hard

How do you optimize a slow SQL query?

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

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

What is table partitioning?