SQL Recursive Sequence Generation

In data analysis, you often need a continuous series of dates, numbers, or structured periods that don't exist in your raw data. Whether you're filling gaps in time-series reports, generating fiscal calendars, or creating test data, the ability to programmatically generate sequences is a foundational skill. Recursive Common Table Expressions (CTEs) provide a powerful, standard SQL method to build these sequences iteratively, offering flexibility that set-based operations sometimes lack.

Anatomy of a Recursive CTE

A recursive CTE is a temporary result set that references itself. It's defined using a WITH clause and consists of two distinct parts: the anchor member and the recursive member, which are united by a UNION ALL. The anchor member is the non-recursive starting point—it provides the initial row(s). The recursive member then references the CTE's own name to build upon the anchor, iteratively adding new rows until a termination condition is met.

Consider generating a simple number sequence from 1 to 5:

WITH RECURSIVE number_sequence AS (
    -- Anchor Member: The starting point
    SELECT 1 AS n
    UNION ALL
    -- Recursive Member: References 'number_sequence'
    SELECT n + 1
    FROM number_sequence
    WHERE n < 5 -- Termination Condition
)
SELECT * FROM number_sequence;

The query executes in cycles: the anchor yields n=1. The recursive member then takes that row (1) and produces n=2. This repeats, using the latest result (2 to get 3, etc.), until the WHERE clause (n < 5) becomes false, stopping the recursion. Understanding this two-part flow is key to controlling sequence generation.

Generating Number and Date Sequences

The simple number pattern scales to more practical uses, like creating a series of consecutive dates. You start with an anchor date and recursively add one day (or any interval) per cycle.

WITH RECURSIVE date_series AS (
    SELECT CAST('2023-01-01' AS DATE) AS generated_date
    UNION ALL
    SELECT DATE_ADD(generated_date, INTERVAL 1 DAY)
    FROM date_series
    WHERE generated_date < '2023-01-10'
)
SELECT * FROM date_series;

This generates every date from January 1st to January 10th, 2023. The termination condition (generated_date < '2023-01-10') ensures the recursion stops after producing the final date. You can modify the interval to WEEK, MONTH, or YEAR to create different grain sequences. This technique is indispensable for creating complete calendar dimensions for joins, ensuring your time-series reports have no gaps even when source data is sparse.

Building Fiscal Calendars and Complex Patterns

Moving beyond simple intervals, recursive CTEs excel at modeling business logic like fiscal calendars, where periods don't align neatly with calendar months. Suppose your fiscal year starts on July 1st, and you need to generate all fiscal months for the year.

WITH RECURSIVE fiscal_calendar AS (
    SELECT
        CAST('2023-07-01' AS DATE) AS fiscal_month_start,
        CAST('2023-07-31' AS DATE) AS fiscal_month_end,
        1 AS fiscal_month_num
    UNION ALL
    SELECT
        DATE_ADD(fiscal_month_start, INTERVAL 1 MONTH),
        DATE_ADD(fiscal_month_end, INTERVAL 1 MONTH),
        fiscal_month_num + 1
    FROM fiscal_calendar
    WHERE fiscal_month_num < 12 -- Stop after 12 fiscal months
)
SELECT
    fiscal_month_num,
    fiscal_month_start,
    fiscal_month_end
FROM fiscal_calendar;

Here, the anchor defines the first fiscal month. The recursive member increments both the start and end dates by one month each iteration, while also counting the periods. This creates a ready-to-use fiscal period table. You can embed further logic in the SELECT to calculate quarter labels or fiscal week numbers, demonstrating how recursion can encapsulate complex sequence rules in a single, readable query.

Termination and Performance Considerations

A recursive CTE will cause an error if it enters an infinite loop. The primary safeguard is a precise termination condition in the WHERE clause of the recursive member, like WHERE n < 100$. However, SQL engines also have built-in safety mechanisms; most databases enforce a default maximum recursion depth (e.g., 100 in SQL Server, configurable with MAXRECURSION`). For long sequences, you must adjust this limit explicitly.

Performance can degrade with very large sequences (tens of thousands of rows) because recursion operates in a row-by-row, iterative manner rather than as a pure set-based operation. Each iteration is a separate logical step. For performance-critical generation of large sequences, it's worth comparing the recursive method to built-in functions like PostgreSQL's GENERATE_SERIES(), which is optimized and non-recursive. The recursive CTE's strength lies in its standard SQL compliance (available in MySQL, SQLite, SQL Server, etc.) and its ability to handle complex, logic-driven iterations that a simple series function cannot.

Practical Application: Gap Filling in Time-Series Data

One of the most powerful applications is gap filling. Imagine you have sporadic sales data and need a report with entries for every day, showing zero on days with no sales. A recursive CTE generates the complete date range, which you then LEFT JOIN to your fact table.

WITH RECURSIVE all_dates AS (
    SELECT MIN(sale_date) AS seq_date FROM sales
    UNION ALL
    SELECT DATE_ADD(seq_date, INTERVAL 1 DAY)
    FROM all_dates
    WHERE seq_date < (SELECT MAX(sale_date) FROM sales)
)
SELECT
    ad.seq_date,
    COALESCE(SUM(s.amount), 0) AS total_sales
FROM all_dates ad
LEFT JOIN sales s ON ad.seq_date = s.sale_date
GROUP BY ad.seq_date
ORDER BY ad.seq_date;

This pattern ensures continuity in your results. The anchor uses the MIN() date from your data, and the recursion continues until it reaches the MAX() date, creating a scaffold. This "sequence as scaffold" technique is also used for generating surrogate dimension tables (like a numbers table for splitting strings) directly within a query, reducing dependency on pre-built static tables.

Common Pitfalls

1. Infinite Recursion from Missing/Vague Termination: The most common error is writing a recursive member that never makes the WHERE clause false. For example, using WHERE n <= 10 when you start at n=1 and add 1 each time is correct. But if you mistakenly wrote WHERE n != 10, the sequence would pass 10 and continue forever (or until the max recursion limit). Correction: Always ensure your termination condition uses a strict inequality (<, <=) that will be definitively met, or a counter that increments toward a fixed limit.

1. Assuming Set-Based Semantics in the Recursive Member: Remember, the recursive member operates on the result set from the previous iteration only, not the entire accumulated result. A mistake is trying to reference a running total from all prior rows directly within the recursion. Correction: If you need a running total or complex aggregation over the whole sequence, perform the recursion to generate the base sequence first, then in an outer query, use window functions (e.g., SUM() OVER()) to compute aggregates across the full set.

1. Neglecting Database-Specific Limits and Syntax: The core WITH RECURSIVE syntax is standard, but limits and options vary. SQL Server uses OPTION (MAXRECURSION 0) to override its 100-row default, while PostgreSQL has no hard default but may suffer performance issues. In MySQL, recursive CTEs were only introduced in version 8.0. Correction: Know your database's recursion depth default and how to change it. Always test the upper bounds of your sequence to avoid unexpected termination.

1. Overusing Recursion When Simpler Methods Exist: If you only need a simple list of 100 numbers and your database has a built-in function like GENERATE_SERIES (PostgreSQL) or a system table, using it is more efficient and readable. Correction: Use recursive CTEs for sequences with complex logic, irregular intervals, or when you need maximum portability across database systems without built-in functions.

Summary

• A recursive CTE builds sequences iteratively using an anchor member for the starting row and a recursive member that references itself, joined by UNION ALL.
• The key to control is a precise termination condition in the recursive member's WHERE clause, preventing infinite loops within database-imposed recursion limits.
• This technique is ideal for generating custom date ranges, number sequences, and business logic-driven patterns like fiscal calendars, all within standard SQL.
• For large-scale sequence generation, be mindful of performance and compare with optimized, built-in functions like GENERATE_SERIES where available.
• The most critical practical application is gap filling in reports, where a recursively generated date scaffold is left-joined to sparse fact data to ensure a continuous series.