Generating Number and Date Series
Using recursion to produce sequences for gap-filling and calendars.
Recursion Without a Hierarchy
Recursive CTEs are not only for trees. A second major use is generating sequences: a run of numbers, or every date in a range. Interviewers ask this when a problem needs gap filling — producing rows that do not exist in any table.
The classic prompt: "Show sales per day for the month, including days with zero sales." You cannot show a missing day unless you first generate all the days.
A Simple Number Series
The anchor seeds the first number; the recursive member adds one each iteration; a WHERE in the recursive member stops it. This generates 1 through 10.
WITH RECURSIVE nums AS (
SELECT 1 AS n
UNION ALL
SELECT n + 1 FROM nums WHERE n < 10
)
SELECT n FROM nums;All lessons in this course
- Anchor and Recursive Members
- Traversing an Org Chart
- Generating Number and Date Series
- Avoiding Infinite Recursion