How do you approach SQL: Date Spine via Recursive CTE?

Postgres: SELECT day::date FROM generate_series('start'::date, 'end'::date, '1 day') day.

What are common mistakes on SQL: Date Spine via Recursive CTE?

Filtering on an aggregate in WHERE rather than HAVING. An inner join where a left join is meant, which silently drops the rows the question was about - the customers with zero orders. COUNT(*) versus COUNT(column). The second skips nulls, and that difference is often the entire question.

gitGood.dev

← Back to problems

SQL: Date Spine via Recursive CTE

MediumDatabases

Description

> This problem is paper-only - see explanation for the canonical SQL solution. Build a "date spine" - one row per day - between two ISO dates inclusive, useful for filling gaps in time-series reports. In Postgres you'd use `generate_series`; in MySQL / SQL Server you'd use a recursive CTE. **For the auto-grader:** implement `dateSpine(start, end)` returning `["YYYY-MM-DD"]` ascending. Cap output at 1000 rows.

Example 1

Input: start = "2026-01-01", end = "2026-01-03"

Output: ["2026-01-01","2026-01-02","2026-01-03"]

Constraints

- start <= end (otherwise return []) - end - start <= 999 days

SQL: Date Spine via Recursive CTE: approach, complexity and pitfalls

A medium sql problem, graded against 4 test cases (2 of them hidden).

The pattern: SQL

Query-writing problems - joins, aggregation, window functions, and query tuning.

Reach for it when you see: A schema and a question about the data rather than a function signature.

More SQLproblems →

If you are stuck

Show the 2-step hint ladder

Postgres: SELECT day::date FROM generate_series('start'::date, 'end'::date, '1 day') day.
MySQL / SQL Server: WITH RECURSIVE dates AS (SELECT start AS d UNION ALL SELECT d + 1 FROM dates WHERE d < end) SELECT d FROM dates.

The optimal approach

Show the approach: Postgres (idiomatic):

```sql

SELECT day::date AS d

FROM generate_series($1::date, $2::date, INTERVAL '1 day') day

ORDER BY d;

```

MySQL 8+ / SQL Server (no generate_series):

```sql

WITH RECURSIVE dates AS (

SELECT CAST($1 AS DATE) AS d

UNION ALL

SELECT DATE_ADD(d, INTERVAL 1 DAY)

FROM dates

WHERE d < CAST($2 AS DATE)

)

SELECT d FROM dates ORDER BY d;

-- MySQL: SET cte_max_recursion_depth = 10000; if needed.

```

Common use: LEFT JOIN actual events to the spine to zero-fill missing days. Without the spine, days with no activity are dropped from charts.

The full reference solution in every supported language stays in the editor above - reveal it there once you have had a real attempt.

Common SQL mistakes

These apply to the pattern as a whole, not just this problem.

Filtering on an aggregate in `WHERE` rather than `HAVING`.
An inner join where a left join is meant, which silently drops the rows the question was about - the customers with zero orders.
`COUNT(*)` versus `COUNT(column)`. The second skips nulls, and that difference is often the entire question.

← Back to problems

SQL: Date Spine via Recursive CTE

MediumDatabases

Description

Example 1

Input: start = "2026-01-01", end = "2026-01-03"

Output: ["2026-01-01","2026-01-02","2026-01-03"]

Constraints

- start <= end (otherwise return []) - end - start <= 999 days

SQL: Date Spine via Recursive CTE: approach, complexity and pitfalls

A medium sql problem, graded against 4 test cases (2 of them hidden).

The pattern: SQL

Query-writing problems - joins, aggregation, window functions, and query tuning.

Reach for it when you see: A schema and a question about the data rather than a function signature.

More SQLproblems →

If you are stuck

Show the 2-step hint ladder

Postgres: SELECT day::date FROM generate_series('start'::date, 'end'::date, '1 day') day.
MySQL / SQL Server: WITH RECURSIVE dates AS (SELECT start AS d UNION ALL SELECT d + 1 FROM dates WHERE d < end) SELECT d FROM dates.

The optimal approach

Show the approach: Postgres (idiomatic):

```sql

SELECT day::date AS d

FROM generate_series($1::date, $2::date, INTERVAL '1 day') day

ORDER BY d;

```

MySQL 8+ / SQL Server (no generate_series):

```sql

WITH RECURSIVE dates AS (

SELECT CAST($1 AS DATE) AS d

UNION ALL

SELECT DATE_ADD(d, INTERVAL 1 DAY)

FROM dates

WHERE d < CAST($2 AS DATE)

)

SELECT d FROM dates ORDER BY d;

-- MySQL: SET cte_max_recursion_depth = 10000; if needed.

```

Common use: LEFT JOIN actual events to the spine to zero-fill missing days. Without the spine, days with no activity are dropped from charts.

The full reference solution in every supported language stays in the editor above - reveal it there once you have had a real attempt.

Common SQL mistakes

These apply to the pattern as a whole, not just this problem.

Filtering on an aggregate in `WHERE` rather than `HAVING`.
An inner join where a left join is meant, which silently drops the rows the question was about - the customers with zero orders.
`COUNT(*)` versus `COUNT(column)`. The second skips nulls, and that difference is often the entire question.