0Pricing
SQL Interview Prep · Lesson

Generating Number and Date Series

Using recursion to produce sequences for gap-filling and calendars.

Generating Number and Date Series is a free SQL Interview Prep lesson on CoddyKit — lesson 3 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Interview Prep learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

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;

The Termination Predicate

Unlike the org chart, a number series has no natural leaf to stop at — you could increment forever. So you must add an explicit stopping condition in the recursive member: WHERE n < 10.

When n reaches 10, the next iteration's WHERE filters out the only candidate row, the recursive member returns nothing, and recursion halts. Forgetting this guard is the number-one cause of runaway recursion in interviews.

Parameterizing the Range

Make the series flexible by driving the bound from a value or variable. Here we generate 1 to N where N is supplied. The same shape produces 0-based or stepped series — just change the anchor and the increment.

WITH RECURSIVE nums AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 2 FROM nums WHERE n + 2 <= 99
)
SELECT n FROM nums;  -- odd numbers 1,3,5,...,99

Generating a Date Series

Swap integer math for date math and you get a calendar. The anchor is the start date; the recursive member adds one day until it passes the end date.

Syntax for adding a day varies by dialect — this Postgres-style form uses an interval.

WITH RECURSIVE cal AS (
    SELECT DATE '2024-01-01' AS d
    UNION ALL
    SELECT d + INTERVAL '1 day'
    FROM cal
    WHERE d < DATE '2024-01-31'
)
SELECT d FROM cal;

Gap Filling With a LEFT JOIN

Now combine the calendar with real data. Generate every day, then LEFT JOIN the sales table so missing days appear with a NULL that you turn into 0 with COALESCE.

This two-step pattern — generate the spine, then left-join the facts — is the heart of every gap-filling answer.

WITH RECURSIVE cal AS (
    SELECT DATE '2024-01-01' AS d
    UNION ALL
    SELECT d + INTERVAL '1 day' FROM cal
    WHERE d < DATE '2024-01-07'
)
SELECT cal.d, COALESCE(SUM(s.amount), 0) AS total
FROM cal
LEFT JOIN sales s ON s.sale_date = cal.d
GROUP BY cal.d
ORDER BY cal.d;

Monthly and Weekly Spines

Change the increment to build coarser calendars. Add INTERVAL '1 month' for a month spine or INTERVAL '7 day' for weeks. Useful when an interviewer wants a per-month report that includes empty months.

WITH RECURSIVE months AS (
    SELECT DATE '2024-01-01' AS m
    UNION ALL
    SELECT m + INTERVAL '1 month' FROM months
    WHERE m < DATE '2024-12-01'
)
SELECT m FROM months;

Dialect Differences in Date Math

Date arithmetic is the least portable part of these queries. Know the variants:

  • Postgres: d + INTERVAL '1 day'.
  • MySQL: DATE_ADD(d, INTERVAL 1 DAY).
  • SQL Server: DATEADD(DAY, 1, d).
  • SQLite: date(d, '+1 day').

Mentioning that the recursion structure is identical and only the date function changes is a strong, dialect-aware answer.

Recursion vs generate_series

Postgres ships a built-in generate_series() that produces numbers or dates without recursion, and it is faster and clearer:

SELECT generate_series(DATE '2024-01-01', DATE '2024-01-31', INTERVAL '1 day');

If the interviewer's database supports it, prefer it. But many engines (MySQL, SQL Server before recent versions) lack it — that is exactly when the recursive CTE is the portable fallback.

Watch the Recursion Limit

Generating a large series can hit the engine's recursion cap. SQL Server defaults to MAXRECURSION 100, so a 365-day calendar fails unless you append OPTION (MAXRECURSION 0) to lift the limit.

Postgres has no fixed cap but a runaway series with a wrong predicate can run until it exhausts memory. Always confirm your termination predicate is correct before scaling up.

-- SQL Server: lift the 100-row recursion cap
-- ...recursive CTE here...
SELECT * FROM cal
OPTION (MAXRECURSION 0);

Cross-Joining the Series

A generated series is often just an ingredient. Once you have a numbers CTE, CROSS JOIN it to expand or explode rows — for example, to repeat each order row by its quantity, or to fan a date range out per customer.

Recognizing that recursion produces a reusable building block, not just a final answer, is what separates a polished interview response from a rote one.

WITH RECURSIVE nums AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM nums WHERE n < 10
)
SELECT o.order_id, nums.n AS unit
FROM orders o
JOIN nums ON nums.n <= o.quantity;

Quick Check

Why is the stopping predicate critical in a number/date series?

Recap

Recursion can manufacture rows that do not exist in any table:

  • Seed the first value in the anchor, increment in the recursive member.
  • Always add an explicit termination predicate — series have no natural end.
  • Build a date/number spine, then LEFT JOIN facts and COALESCE for gap filling.
  • Prefer generate_series where available; mind MAXRECURSION on SQL Server.

Next: the safety techniques that keep recursion from running away.

Frequently asked questions

Is the “Generating Number and Date Series” lesson free?

Yes — the full text of “Generating Number and Date Series” is free to read here on the web, and the SQL Interview Prep course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Interview Prep course, upgrade to CoddyKit PRO.

What will I learn in “Generating Number and Date Series”?

Using recursion to produce sequences for gap-filling and calendars. You practise SQL Interview Prep with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Interview Prep?

No prior experience is required. SQL Interview Prep on CoddyKit is structured for beginners through advanced learners; this is — lesson 3 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Generating Number and Date Series” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Interview Prep lesson?

Yes. Every SQL Interview Prep lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. Anchor and Recursive Members
  2. Traversing an Org Chart
  3. Generating Number and Date Series
  4. Avoiding Infinite Recursion
← Back to SQL Interview Prep