SQL exercises with solutions: recursive CTEs

Updated on 22 September 2026

A recursive CTE calls itself to generate a sequence or walk a hierarchy. Always add a stop condition. These 11 exercises range from level 7 to level 8; they use the syntax of SQLite, SpeedQL’s SQL engine.

Tip: A starting part, UNION ALL, then the part that calls itself with a WHERE that stops.

Read the “Recursive CTEs” card in the cheat sheet

Exercise 1 · level 7

Show each employee with their hierarchy level: 0 for the one with no manager, 1 for their direct reports, and so on (columns: name, level). Use a recursive CTE.

Table `staff` (10 rows)
id	name	department	salary	manager_id	hired
1	Alice	Finance	52000	NULL	2015-03-01
2	Bob	IT	41000	1	2018-06-15
3	Claire	Finance	38000	1	2019-01-10
4	David	HR	29000	1	2020-09-01
5	Emma	IT	50000	2	2017-11-20
6	Farid	IT	47000	2	2021-02-14
7	Gaelle	HR	33000	4	2022-05-30
8	Hugo	Finance	44000	3	2016-08-08
9	Iris	IT	47000	5	2023-01-09
10	Jules	Finance	44000	3	2024-03-18

Show the hint

Topics to use: WHERE (filters), JOIN, NULL, COALESCE, Recursive CTEs.

Query structure:

WITH RECURSIVE …(…, …, …) AS (
  SELECT …, …, …
  FROM …
  WHERE … IS NULL
  UNION ALL SELECT …, …, … + …
  FROM … …
  JOIN … ON … = …)
SELECT …, …
FROM …

Show the solution

WITH RECURSIVE t(id, name, level) AS (
  SELECT id, name, 0
  FROM staff
  WHERE manager_id IS NULL
  UNION ALL SELECT s.id, s.name, t.level + 1
  FROM staff s
  JOIN t ON s.manager_id = t.id)
SELECT name, level
FROM t;

Expected result (10 rows):

name	level
Alice	0
Bob	1
Claire	1
David	1
Emma	2
Farid	2
Hugo	2
Jules	2
Gaelle	2
Iris	3

Exercise 2 · level 7

For each year from 2012 to 2022, show the year and the number of movies released that year (0 if none). Use a recursive CTE to generate the years.

Table `movies` (10 rows)
id	title	genre	year	duration	rating	director_id
1	Night Train	Thriller	2015	118	7.8	1
2	Blue Harbor	Drama	2018	102	7.1	2
3	Paper Moon City	Comedy	2012	95	6.4	5
4	Silent Peak	Drama	2020	131	8.2	3
5	Last Signal	Sci-Fi	2019	142	7.5	1
6	Summer Keys	Comedy	2016	88	5.9	4
7	Iron Garden	Sci-Fi	2021	125	8	3
8	Dust and Gold	Western	2014	110	6.8	2
9	The Quiet Hour	Drama	2022	97	7.4	5
10	Deep Current	Thriller	2017	105	6.9	NULL

Show the hint

Topics to use: WHERE (filters), COUNT, SUM, AVG, MIN, MAX, Subqueries, Recursive CTEs.

Query structure:

WITH RECURSIVE …(…) AS (
  SELECT …
  UNION ALL SELECT … + …
  FROM …
  WHERE … < …)
SELECT …, (SELECT COUNT(*) FROM … WHERE … = …)
FROM …

Show the solution

WITH RECURSIVE y(n) AS (
  SELECT 2012
  UNION ALL SELECT n + 1
  FROM y
  WHERE n < 2022)
SELECT n, (SELECT COUNT(*) FROM movies WHERE year = n)
FROM y;

Expected result (11 rows):

n	(SELECT COUNT(*) FROM movies WHERE year = n)
2012	1
2013	0
2014	1
2015	1
2016	1
2017	1
2018	1
2019	1
2020	1
2021	1
2022	1

Exercise 3 · level 7

For each day from 2025-03-01 to 2025-03-10, show the date and the number of loans started that day (0 if none). Use a recursive CTE to generate the dates.

Table `loans` (12 rows)
id	book_id	member_id	loan_date	return_date
1	1	1	2025-01-05	2025-01-19
2	3	2	2025-01-10	2025-02-02
3	5	1	2025-02-01	2025-02-10
4	7	3	2025-02-03	NULL
5	3	4	2025-02-15	2025-03-01
6	2	2	2025-03-02	2025-03-30
7	8	5	2025-03-05	NULL
8	1	3	2025-03-10	2025-03-18
9	6	1	2025-03-20	2025-04-15
10	3	5	2025-04-01	NULL
11	9	4	2025-04-05	2025-04-12
12	10	2	2025-04-08	2025-04-20

Show the hint

Topics to use: WHERE (filters), COUNT, SUM, AVG, MIN, MAX, Subqueries, Text and dates, Recursive CTEs.

Query structure:

WITH RECURSIVE …(…) AS (
  SELECT …
  UNION ALL SELECT DATE(…, …)
  FROM …
  WHERE … < …)
SELECT …, (SELECT COUNT(*) FROM … WHERE … = …)
FROM …

Show the solution

WITH RECURSIVE d(day) AS (
  SELECT '2025-03-01'
  UNION ALL SELECT date(day, '+1 day')
  FROM d
  WHERE day < '2025-03-10')
SELECT day, (SELECT COUNT(*) FROM loans WHERE loan_date = day)
FROM d;

Expected result (10 rows):

day	(SELECT COUNT(*) FROM loans WHERE loan_date = day)
2025-03-01	0
2025-03-02	1
2025-03-03	0
2025-03-04	0
2025-03-05	1
2025-03-06	0
2025-03-07	0
2025-03-08	0
2025-03-09	0
2025-03-10	1

Exercise 4 · level 7

The league has 5 rounds: round 1 starts on 2025-08-02 and each round starts 7 days after the previous one. For each round, show its number, its start date and the number of goals scored that day or the next day (0 if none). Use a recursive CTE.

Table `matches` (10 rows)
id	played_on	home_id	away_id	home_goals	away_goals
1	2025-08-02	1	2	2	1
2	2025-08-03	3	4	0	0
3	2025-08-09	5	1	1	3
4	2025-08-10	2	3	2	2
5	2025-08-16	4	5	3	1
6	2025-08-17	1	3	1	0
7	2025-08-23	2	4	0	1
8	2025-08-24	3	5	2	3
9	2025-08-30	4	1	1	1
10	2025-08-31	5	2	0	2

Show the hint

Topics to use: WHERE (filters), COUNT, SUM, AVG, MIN, MAX, Subqueries, NULL, COALESCE, Text and dates, Recursive CTEs.

Query structure:

WITH RECURSIVE …(…, …) AS (
  SELECT …, …
  UNION ALL SELECT … + …, DATE(…, …)
  FROM …
  WHERE … < …)
SELECT …, …, COALESCE((SELECT SUM(… + …) FROM … WHERE … BETWEEN … AND DATE(…, …)), …)
FROM …

Show the solution

WITH RECURSIVE r(n, d) AS (
  SELECT 1, '2025-08-02'
  UNION ALL SELECT n + 1, date(d, '+7 day')
  FROM r
  WHERE n < 5)
SELECT n, d, COALESCE((SELECT SUM(home_goals + away_goals) FROM matches WHERE played_on BETWEEN d AND date(d, '+1 day')), 0)
FROM r;

Expected result (5 rows):

n	d	COALESCE((SELECT SUM(home_goals + away_goals) FROM matches WHERE played_on BETWEEN d AND date(d, '+1 day')), 0)
1	2025-08-02	3
2	2025-08-09	8
3	2025-08-16	5
4	2025-08-23	6
5	2025-08-30	4

Exercise 5 · level 7

From CDG, find the airports you can reach by taking at most 2 flights (ignoring schedules): show the code of each airport other than CDG and the minimum number of flights to reach it. Use a recursive CTE.

Table `flights` (12 rows)
id	airline	origin	dest	departs	duration_min	price	seats_sold	capacity
1	SkyJet	CDG	MAD	2025-06-01 08:10	125	89	150	180
2	AirNova	CDG	LIS	2025-06-01 11:40	155	120	160	170
3	BlueWing	LYS	FCO	2025-06-02 07:30	95	75	110	150
4	SkyJet	MAD	CDG	2025-06-02 18:20	120	95	170	180
5	AirNova	BER	CDG	2025-06-03 09:05	110	105	140	160
6	BlueWing	NCE	BER	2025-06-03 13:50	130	140	90	150
7	SkyJet	CDG	FCO	2025-06-04 06:45	135	99	175	180
8	AirNova	LIS	MAD	2025-06-04 16:15	75	65	60	120
9	BlueWing	FCO	LYS	2025-06-05 20:30	100	82	130	150
10	SkyJet	CDG	BER	2025-06-05 10:00	105	110	120	180
11	AirNova	MAD	LIS	2025-06-06 12:25	80	70	95	120
12	BlueWing	LYS	MAD	2025-06-06 15:40	115	79	100	150

Show the hint

Topics to use: WHERE (filters), COUNT, SUM, AVG, MIN, MAX, GROUP BY, JOIN, Recursive CTEs.

Query structure:

WITH RECURSIVE …(…, …) AS (
  SELECT …, …
  UNION SELECT …, … + …
  FROM …
  JOIN … … ON … = …
  WHERE … < …)
SELECT …, MIN(…)
FROM …
WHERE … <> …
GROUP BY …

Show the solution

WITH RECURSIVE r(code, n) AS (
  SELECT 'CDG', 0
  UNION SELECT f.dest, r.n + 1
  FROM r
  JOIN flights f ON f.origin = r.code
  WHERE r.n < 2)
SELECT code, MIN(n)
FROM r
WHERE code <> 'CDG'
GROUP BY code;

Expected result (5 rows):

code	MIN(n)
BER	1
FCO	1
LIS	1
LYS	2
MAD	1

Exercise 6 · level 7

For each day from 2025-06-29 to 2025-07-05, show the day and the total rain recorded across all cities (0 if there is no reading that day). Use a recursive CTE to generate the days.

Table `readings` (15 rows)
id	city	day	temp_max	temp_min	rain_mm
1	Paris	2025-07-01	24	15	0
2	Paris	2025-07-02	27	17	0
3	Paris	2025-07-03	22	16	4.5
4	Paris	2025-07-04	19	14	12
5	Paris	2025-07-05	23	13	0
6	Lyon	2025-07-01	26	16	0
7	Lyon	2025-07-02	29	18	0
8	Lyon	2025-07-03	31	19	0
9	Lyon	2025-07-04	24	17	8.5
10	Lyon	2025-07-05	22	15	3
11	Marseille	2025-07-01	30	21	0
12	Marseille	2025-07-02	32	22	0
13	Marseille	2025-07-03	33	23	0
14	Marseille	2025-07-04	29	21	0
15	Marseille	2025-07-05	28	20	0

Show the hint

Topics to use: WHERE (filters), COUNT, SUM, AVG, MIN, MAX, Subqueries, NULL, COALESCE, Text and dates, Recursive CTEs.

Query structure:

WITH RECURSIVE …(…) AS (
  SELECT …
  UNION ALL SELECT DATE(…, …)
  FROM …
  WHERE … < …)
SELECT …, COALESCE((SELECT SUM(…) FROM … … WHERE … = …), …)
FROM …

Show the solution

WITH RECURSIVE d(day) AS (
  SELECT '2025-06-29'
  UNION ALL SELECT date(day, '+1 day')
  FROM d
  WHERE day < '2025-07-05')
SELECT day, COALESCE((SELECT SUM(rain_mm) FROM readings r WHERE r.day = d.day), 0)
FROM d;

Expected result (7 rows):

day	COALESCE((SELECT SUM(rain_mm) FROM readings r WHERE r.day = d.day), 0)
2025-06-29	0
2025-06-30	0
2025-07-01	0
2025-07-02	0
2025-07-03	4.5
2025-07-04	20.5
2025-07-05	3

Exercise 7 · level 7

For each night from 2025-07-01 to 2025-07-10, show the date and the number of occupied rooms (arrival on or before that day and departure after that day). Use a recursive CTE to generate the dates.

Table `bookings` (11 rows)
id	room_id	guest_id	check_in	check_out
1	2	1	2025-07-01	2025-07-04
2	5	2	2025-07-02	2025-07-05
3	8	3	2025-07-03	2025-07-06
4	3	4	2025-07-05	2025-07-12
5	6	5	2025-07-06	2025-07-08
6	1	2	2025-07-08	2025-07-10
7	9	3	2025-07-10	2025-07-11
8	7	5	2025-07-11	2025-07-15
9	4	1	2025-07-14	2025-07-16
10	6	4	2025-07-15	2025-07-19
11	6	5	2025-07-16	2025-07-18

Show the hint

Topics to use: WHERE (filters), COUNT, SUM, AVG, MIN, MAX, Subqueries, Text and dates, Recursive CTEs.

Query structure:

WITH RECURSIVE …(…) AS (
  SELECT …
  UNION ALL SELECT DATE(…, …)
  FROM …
  WHERE … < …)
SELECT …, (SELECT COUNT(*) FROM … … WHERE … <= … AND … > …)
FROM …

Show the solution

WITH RECURSIVE d(day) AS (
  SELECT '2025-07-01'
  UNION ALL SELECT date(day, '+1 day')
  FROM d
  WHERE day < '2025-07-10')
SELECT day, (SELECT COUNT(*) FROM bookings b WHERE b.check_in <= d.day AND b.check_out > d.day)
FROM d;

Expected result (10 rows):

day	(SELECT COUNT(*) FROM bookings b WHERE b.check_in <= d.day AND b.check_out > d.day)
2025-07-01	1
2025-07-02	2
2025-07-03	3
2025-07-04	2
2025-07-05	2
2025-07-06	2
2025-07-07	2
2025-07-08	2
2025-07-09	2
2025-07-10	2

Exercise 8 · level 7

For each month from 2025-01 to 2025-06 (format YYYY-MM), show the month and the number of orders placed (0 if none). Use a recursive CTE to generate the months.

Table `orders` (8 rows)
id	customer_id	order_date	status
1	1	2025-01-05	shipped
2	2	2025-01-12	shipped
3	1	2025-02-03	paid
4	3	2025-02-10	cancelled
5	4	2025-02-20	shipped
6	5	2025-03-02	shipped
7	2	2025-03-15	paid
8	3	2025-03-28	shipped

Show the hint

Topics to use: WHERE (filters), COUNT, SUM, AVG, MIN, MAX, Subqueries, Text and dates, Recursive CTEs.

Query structure:

WITH RECURSIVE …(…) AS (
  SELECT …
  UNION ALL SELECT STRFTIME(…, … || …, …)
  FROM …
  WHERE … < …)
SELECT …, (SELECT COUNT(*) FROM … … WHERE STRFTIME(…, …) = …)
FROM …

Show the solution

WITH RECURSIVE m(month) AS (
  SELECT '2025-01'
  UNION ALL SELECT strftime('%Y-%m', month || '-01', '+1 month')
  FROM m
  WHERE month < '2025-06')
SELECT month, (SELECT COUNT(*) FROM orders o WHERE strftime('%Y-%m', o.order_date) = m.month)
FROM m;

Expected result (6 rows):

month	(SELECT COUNT(*) FROM orders o WHERE strftime('%Y-%m', o.order_date) = m.month)
2025-01	2
2025-02	3
2025-03	3
2025-04	0
2025-05	0
2025-06	0

Exercise 9 · level 7

For each month from 2025-02 to 2025-06 (format YYYY-MM), show the month and the hours of the tasks finished that month (0 if none). Use a recursive CTE to generate the months.

Table `tasks` (10 rows)
id	project_id	dev_id	title	hours	status	done_on
1	1	1	Landing page	12	done	2025-03-10
2	1	3	Data model	20	done	2025-03-20
3	1	2	Login	8	doing	NULL
4	2	4	ETL job	16	done	2025-04-02
5	2	5	CI pipeline	6	done	2025-03-15
6	3	1	Dashboard	14	todo	NULL
7	3	3	Report	10	done	2025-04-25
8	4	2	API	18	doing	NULL
9	4	5	Monitoring	9	todo	NULL
10	4	4	Forecast	22	done	2025-05-05

Show the hint

Topics to use: WHERE (filters), COUNT, SUM, AVG, MIN, MAX, Subqueries, NULL, COALESCE, Text and dates, Recursive CTEs.

Query structure:

WITH RECURSIVE …(…) AS (
  SELECT …
  UNION ALL SELECT STRFTIME(…, … || …, …)
  FROM …
  WHERE … < …)
SELECT …, COALESCE((SELECT SUM(…) FROM … … WHERE STRFTIME(…, …) = …), …)
FROM …

Show the solution

WITH RECURSIVE m(month) AS (
  SELECT '2025-02'
  UNION ALL SELECT strftime('%Y-%m', month || '-01', '+1 month')
  FROM m
  WHERE month < '2025-06')
SELECT month, COALESCE((SELECT SUM(hours) FROM tasks t WHERE strftime('%Y-%m', t.done_on) = m.month), 0)
FROM m;

Expected result (5 rows):

month	COALESCE((SELECT SUM(hours) FROM tasks t WHERE strftime('%Y-%m', t.done_on) = m.month), 0)
2025-02	0
2025-03	38
2025-04	26
2025-05	22
2025-06	0

Exercise 10 · level 7

For each day from 2025-01-01 to 2025-01-07, show the date and the sum of that day’s transactions, all accounts together (0 if there are none). Use a recursive CTE to generate the dates.

Table `transactions` (14 rows)
id	account_id	made_on	amount	label
1	1	2025-01-02	2500	salary
2	1	2025-01-05	-60	groceries
3	1	2025-01-12	-800	rent
4	2	2025-01-15	500	transfer
5	3	2025-01-03	1900	salary
6	3	2025-01-20	-120	groceries
7	3	2025-02-01	-950	rent
8	4	2025-01-25	2100	salary
9	4	2025-02-03	-45	restaurant
10	1	2025-02-02	2600	salary
11	1	2025-02-06	-75	groceries
12	5	2025-02-10	30	interest
13	2	2025-02-15	500	transfer
14	4	2025-02-18	-600	rent

Show the hint

Topics to use: WHERE (filters), COUNT, SUM, AVG, MIN, MAX, Subqueries, NULL, COALESCE, Text and dates, Recursive CTEs.

Query structure:

WITH RECURSIVE …(…) AS (
  SELECT …
  UNION ALL SELECT DATE(…, …)
  FROM …
  WHERE … < …)
SELECT …, COALESCE((SELECT SUM(…) FROM … … WHERE … = …), …)
FROM …

Show the solution

WITH RECURSIVE d(day) AS (
  SELECT '2025-01-01'
  UNION ALL SELECT date(day, '+1 day')
  FROM d
  WHERE day < '2025-01-07')
SELECT day, COALESCE((SELECT SUM(amount) FROM transactions t WHERE t.made_on = d.day), 0)
FROM d;

Expected result (7 rows):

day	COALESCE((SELECT SUM(amount) FROM transactions t WHERE t.made_on = d.day), 0)
2025-01-01	0
2025-01-02	2500
2025-01-03	1900
2025-01-04	0
2025-01-05	-60
2025-01-06	0
2025-01-07	0

Exercise 11 · level 8

For each employee who has at least one report, show their name and the total number of their reports, direct and indirect. Use a recursive CTE.

Table `staff` (10 rows)
id	name	department	salary	manager_id	hired
1	Alice	Finance	52000	NULL	2015-03-01
2	Bob	IT	41000	1	2018-06-15
3	Claire	Finance	38000	1	2019-01-10
4	David	HR	29000	1	2020-09-01
5	Emma	IT	50000	2	2017-11-20
6	Farid	IT	47000	2	2021-02-14
7	Gaelle	HR	33000	4	2022-05-30
8	Hugo	Finance	44000	3	2016-08-08
9	Iris	IT	47000	5	2023-01-09
10	Jules	Finance	44000	3	2024-03-18

Show the hint

Topics to use: COUNT, SUM, AVG, MIN, MAX, GROUP BY, JOIN, HAVING, Recursive CTEs.

Query structure:

WITH RECURSIVE …(…, …) AS (
  SELECT …, …
  FROM …
  UNION ALL SELECT …, …
  FROM … …
  JOIN … ON … = …)
SELECT …, COUNT(*) - …
FROM …
JOIN … … ON … = …
GROUP BY …
HAVING COUNT(*) > …

Show the solution

WITH RECURSIVE d(root, id) AS (
  SELECT id, id
  FROM staff
  UNION ALL SELECT d.root, s.id
  FROM staff s
  JOIN d ON s.manager_id = d.id)
SELECT m.name, COUNT(*) - 1
FROM d
JOIN staff m ON m.id = d.root
GROUP BY d.root
HAVING COUNT(*) > 1;

Expected result (5 rows):

name	COUNT(*) - 1
Alice	9
Bob	3
Claire	2
David	1
Emma	1

Practice with automatic checking

In SpeedQL, you write your query and it is checked straight away, on these tables and then on a hidden dataset.

Practice on the 18 “Recursive CTEs” questions