Question 1

What is a self join?

Accepted Answer

**Target Audience:** Beginners who know a basic join and want to see a table joined to itself, and candidates preparing for 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. Self joins are plain standard SQL and work the same way in MySQL and SQL Server.

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

Question 2

Recursive CTE for an org chart — how do you list all reports (direct + indirect) of a manager?

Accepted Answer

**Target Audience:** Developers who know JOINs and have seen a basic recursive CTE; the applied interview version of "walk a hierarchy".
**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; every query below was run on both and returned the same rows.

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

Question 3

What do GROUP BY ROLLUP and CUBE do?

Accepted Answer

**Target Audience:** Beginners who know `GROUP BY` and want subtotals and grand totals in one query, and candidates preparing for reporting and analytics questions.
**Difficulty:** Medium
**Engines:** PostgreSQL 16.15 for `ROLLUP`, `CUBE` and `GROUPING SETS`; SQLite 3.45.0 (sql.js 1.10.2, the engine the runnable example on this page uses) for the portable `UNION ALL` version, because SQLite has no `ROLLUP`. Both were run for this page. MySQL and SQL Server notes are cited.

> **How to read this

SQLDECODED.

Medium

What is a self join?

Recursive CTE for an org chart — how do you list all reports (direct + indirect) of a manager?

What do GROUP BY ROLLUP and CUBE do?