Final report
- SQL course
- Advanced
- Lesson 23/81
- 5 min
Write a hierarchy report with a recursive CTE.
Final report
In the final report you can do trend analysis with a recursive CTE. Generate a date series and match events.
WITH RECURSIVE days(d) AS (
SELECT DATE '2026-01-01'
UNION ALL
SELECT d + 1 FROM days WHERE d < DATE '2026-01-31'
)
SELECT d, COUNT(e.id) AS events
FROM days d
LEFT JOIN events e ON e.event_date = d.d
GROUP BY d
ORDER BY d;This query gives the event count per day.
WITH RECURSIVE defines a self-referencing temporary result set (CTE) named days; the query after it uses it like a table. d, COUNT(e.id) AS events is selected from days d. events e is joined on e.event_date = d.d, keeping every row of the left table. Rows are grouped by d; aggregate functions are computed per group. The result is sorted by d in ascending order.
Final report — details
The recursive CTE generates a date series. LEFT JOIN brings 0 for days with no events.
Days without events then show up as zero instead of gaps in a chart; COALESCE(count, 0) turns NULL counts into 0.
Practice Final report
Generate all January days with a recursive CTE and compute daily event counts.
Starter code
-- final report
WITH RECURSIVESample solution
WITH RECURSIVE days(d) AS (
SELECT DATE '2026-01-01'
UNION ALL
SELECT d + 1 FROM days WHERE d < DATE '2026-01-31'
)
SELECT d, COUNT(e.id) AS events
FROM days d
LEFT JOIN events e ON e.event_date = d.d
GROUP BY d
ORDER BY d;Expected output
d events 2026-01-01 15 2026-01-02 20
Questions and answers
Review the lesson questions together with their answers. To test yourself, solve the lesson interactively.
1 Add RECURSIVE
WITH ____ days(d) AS (
SELECT DATE '2026-01-01'
UNION ALL
SELECT d + 1 FROM days WHERE d < DATE '2026-01-31'
)
SELECT d, COUNT(e.id) FROM days d LEFT JOIN events e ON e.event_date = d.d GROUP BY d;WITH RECURSIVE days(d) AS (
SELECT DATE '2026-01-01'
UNION ALL
SELECT d + 1 FROM days WHERE d < DATE '2026-01-31'
)
SELECT d, COUNT(e.id) FROM days d LEFT JOIN events e ON e.event_date = d.d GROUP BY d;Explanation: RECURSIVE is the right answer.
2 Final report: match the terms
| Term | Meaning |
|---|---|
recursive CTE |
date series |
LEFT JOIN |
all days |
trend |
trend |
0 empty day |
no events |
3 What does the recursive CTE generate here?
- A list of names
- A sequence of numbers
- A sequence of dates Correct answer
- Random
Explanation: The recursive CTE generates a date series.