SQL
Final report
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.
Exercise Final report
Generate all January days with a recursive CTE and compute daily event counts.
Show example 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;
Test yourself
-
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;
Show answer
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;
-
Final report: match the terms
- recursive CTE
- date series
- LEFT JOIN
- all days
- trend
- trend
- 0 empty day
- no events
-
What does the recursive CTE generate here?
- A list of names
- A sequence of numbers
- A sequence of dates
- Random
Show answer
A sequence of dates — The recursive CTE generates a date series.
Next lesson: What is pivot?
