✏️ Explanatory Question
Level: Hard — A very common reporting need; charts break when days with zero activity simply vanish from the results.
Scenario: A "daily sales" report should show every day of the month. But days with no sales are missing entirely, so the line chart skips them and looks wrong. You need a row for every date, with zero where there were no sales. How do you generate the missing dates?
The problem is that GROUP BY sale_date only produces rows for dates that exist in the data. The fix: generate a complete calendar of dates using a recursive CTE, then LEFT JOIN the actual sales onto it — so every date appears, with 0 where there is no match.
sales (note the gaps)| sale_date | amount |
|---|---|
| 2026-08-01 | 100 |
| 2026-08-03 | 250 |
| 2026-08-04 | 175 |
Aug 2 and Aug 5 have no sales — they are simply absent from the table.
-- Recursively build every date in a range (no source table needed)
WITH RECURSIVE date_series AS (
SELECT DATE('2026-08-01') AS d -- anchor: the start date
UNION ALL
SELECT d + INTERVAL 1 DAY -- add one day each iteration
FROM date_series
WHERE d < '2026-08-05' -- stop condition (the end date)
)
SELECT d FROM date_series;
-- Produces: 2026-08-01, 08-02, 08-03, 08-04, 08-05 How it works: The anchor is the first date. Each recursive pass adds one day to the previous result, and the WHERE d < end condition stops it. This "generates rows from nothing" — a table of dates that never had to exist physically.
WITH RECURSIVE date_series AS (
SELECT DATE('2026-08-01') AS d
UNION ALL
SELECT d + INTERVAL 1 DAY FROM date_series WHERE d < '2026-08-05'
)
SELECT
ds.d AS sale_date,
COALESCE(SUM(s.amount), 0) AS daily_total -- 0 for days with no sales
FROM date_series ds
LEFT JOIN sales s ON s.sale_date = ds.d
GROUP BY ds.d
ORDER BY ds.d;| sale_date | daily_total |
|---|---|
| 2026-08-01 | 100 |
| 2026-08-02 | 0 (gap filled) |
| 2026-08-03 | 250 |
| 2026-08-04 | 175 |
| 2026-08-05 | 0 (gap filled) |
Now every day appears — the chart is continuous and correct.
-- Generate numbers 1 to 10 (useful for buckets, test data, tally tables)
WITH RECURSIVE nums AS (
SELECT 1 AS n
UNION ALL
SELECT n + 1 FROM nums WHERE n < 10
)
SELECT n FROM nums;
-- Dynamic range: every day of the CURRENT month
WITH RECURSIVE cal AS (
SELECT DATE_FORMAT(CURDATE(), '%Y-%m-01') AS d
UNION ALL
SELECT d + INTERVAL 1 DAY FROM cal
WHERE d + INTERVAL 1 DAY <= LAST_DAY(CURDATE())
)
SELECT d FROM cal; Recursion depth limit: A year of dates is 365 rows — fine. But generating a huge series (e.g., every minute for 5 years) can exceed cte_max_recursion_depth (default 1000). Raise it with SET SESSION cte_max_recursion_depth = 100000; when you genuinely need a large series, or use a permanent calendar/numbers table for very frequent use.
Interviewer follow-up: "You run this gap-fill report constantly — is recursion the best choice each time?" → For repeated use, create a permanent calendar table (one row per date, pre-populated for years, indexed). Joining a static calendar table is faster and simpler than regenerating dates recursively on every query. Recursive CTEs are perfect for ad-hoc ranges; a calendar table is better for a core, frequently-used dimension.