Part 88: Generating Number & Date Series with Recursive Queries

Filling missing calendar date gaps in analytics reporting.

1. Generating a Date Range

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

Key Takeaways

• Generate continuous calendar days to perform zero-filled reporting on dates with no activity.

In Part 89, we explore Preventing Infinite Loops in Recursion: CYCLE and Max Limits!