Level 10 · Days with no marketplace orders

GROUP BY can only report values that exist in the data. A day with no orders has no rows, so it never appears. To see it, you need a list of every day to join against: a calendar spine. A recursive CTE can build one.

How WITH RECURSIVE works

WITH RECURSIVE calendar AS (
  SELECT DATE '2025-01-01' AS day          -- anchor: the first row
  UNION ALL
  SELECT CAST(day + INTERVAL 1 DAY AS DATE) -- recursive part: reads the previous rows
  FROM calendar
  WHERE day < DATE '2025-12-31'             -- stop condition
)
SELECT COUNT(*) FROM calendar;              -- 365

The anchor runs once. The recursive part then runs again and again on the rows produced by the last round, until it produces nothing because the WHERE condition fails. Forget the stop condition and the query never ends, so write it first.

The same pattern walks hierarchies. Starting from the CEO (the employee with no manager) and repeatedly joining employees whose manager_id is in the previous round, you get every employee with their depth in the org chart: Catherine Hale at depth 0, and the deepest people in Customer Support at depth 4.

Calendar plus LEFT JOIN

With the spine in place, aggregate orders per day in a second CTE and LEFT JOIN it to the calendar. Days without orders survive the join with NULLs, and COUNT(CASE WHEN d.day IS NULL THEN 1 END) counts them. In 2025 the marketplace had no orders on 11 days in February and none in November, when holiday traffic filled every day.

DuckDB also has a shortcut, SELECT * FROM generate_series(DATE '2025-01-01', DATE '2025-12-31', INTERVAL 1 DAY), but recursion works in almost every database and also handles trees, which a series cannot.

Common mistakes

• Joining on the timestamp: order_date has a time of day, so cast it to DATE before matching days.
• Counting COUNT(d.orders) for zero days: that counts the days with orders.

In SQL Server

Write WITH calendar AS (...) without the word RECURSIVE, use DATEADD(day, 1, day) to step, and note the default limit of 100 recursion rounds. A full year needs OPTION (MAXRECURSION 400) at the end of the statement, or 0 for no limit.