Question 1

How do you aggregate values into a single string?

Accepted Answer

**Target Audience:** Beginners who know `GROUP BY` and want to produce "one row with a list", and candidates who need the dialect differences that interviewers love to ask.
**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, SQL Server and Oracle syntax is cited from their documentation, not run.

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

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 is denormalization and when should you use it?

Accepted Answer

**Target Audience:** Developers who know what normalization is and now face slow reads, plus candidates who need to discuss read/write trade-offs and sync mechanisms precisely.
**Difficulty:** Medium
**Engines:** The SQLite examples ran on SQLite 3.45.0 (sql.js 1.10.2, the page engine). Triggers and materialized views are shown on PostgreSQL 16.15, run in a scratch database for this page. SQLite has triggers but no materialized views.

> **How to read this doc:** Each idea is first explained in

Question 5

What is the difference between OLTP and OLAP databases?

Accepted Answer

**Target Audience:** Beginners who hear "OLTP" and "OLAP" in architecture discussions, and candidates who need to explain why companies run a separate data warehouse.
**Difficulty:** Medium
**Engines:** The SQL examples run on SQLite 3.45.0 (sql.js 1.10.2, the engine this page uses) and returned the same rows on PostgreSQL 16.15; the plan comparison ran on PostgreSQL 16.15. Facts about warehouse products are cited from their documentation, not run here.

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

Question 6

How do you optimize a slow SQL query?

Accepted Answer

**Target Audience:** Developers who own a slow query and want a method instead of guesses, and candidates facing the open-ended "how would you optimise this?" interview question.
**Difficulty:** Hard
**Engines:** PostgreSQL 16.15 (plans and timings quoted here were produced on it) and SQLite 3.45.0 (sql.js 1.10.2, the engine the runnable examples on this page use). The method is the same on MySQL (`EXPLAIN ANALYZE` since 8.0.18, per the [MySQL blog announcing it](https://dev.mysql.com/blog-archi

SQLDECODED.

Medium