SQL Intermediate Lesson 7: Recursive CTEs – Org Charts and Date Series

📎 This article includes 2 downloadable practice files ↓

📘 SQL Intermediate Course · Lesson 7 of 10

A recursive CTE has two parts joined by UNION ALL: a starting row (the anchor) and a step that adds rows based on the previous ones, repeated until no new rows appear.

WITH RECURSIVE team(emp_id, name, lvl) AS (
  SELECT emp_id, name, 0 FROM employees WHERE emp_id = 2          -- anchor: Neha
  UNION ALL
  SELECT e.emp_id, e.name, t.lvl + 1
  FROM employees e JOIN team t ON e.manager_id = t.emp_id          -- step: her reports
)
SELECT * FROM team;

Reporting paths

WITH RECURSIVE p(emp_id, path) AS (
  SELECT emp_id, name FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.emp_id, p.path || ' > ' || e.name FROM employees e JOIN p ON e.manager_id = p.emp_id
)
SELECT path FROM p;   -- Anil Kapoor > Neha Sharma > Arjun Mehta > Rohan Gill

A calendar from nothing

WITH RECURSIVE d(day) AS (
  SELECT '2026-07-01'
  UNION ALL
  SELECT date(day, '+1 day') FROM d WHERE day < '2026-07-31'
)
SELECT day FROM d WHERE day NOT IN (SELECT order_date FROM orders);

Calendars fill gaps for LAG (lesson 4) and zero-sales days in charts.

Syntax: SQL Server and Oracle write WITH without RECURSIVE; string concatenation is + in SQL Server and CONCAT() in MySQL.

Practice

Download shop2.db and this lesson’s exercise file below. Open the database in DB Browser for SQLite (free), paste each task into the Execute SQL tab and compare with the expected result printed under it. Answers are at the bottom of the file.