Gap Detection (Date Series)

Gap Detection (Date Series) is a SQL statement in the CTE (WITH) category. Uses recursive CTE to generate a complete date series and find missing (gap) dates. The syntax is WITH RECURSIVE dates AS (...) SELECT d FROM dates LEFT JOIN data ON ... WHERE data.id IS NULL;. It returns missing dates. A typical example: WITH RECURSIVE all_dates AS ( SELECT DATE('2026-01-01') AS dt UNION ALL SELECT DATE_ADD(dt, INTERVAL 1 DAY) FROM all_dates WHERE dt < '2026-01-31' ) SELECT ad.dt AS missing_date FROM all_dates ad LEFT JOIN orders o ON DATE(o.created_at) = ad.dt WHERE o.id IS NULL; -- Finds dates with no orders (gaps in the time series) A close relative is WITH (CTE), which common Table Expression — define a named temporary result set that can be referenced in the main query. A close relative is Recursive CTE, which a CTE that references itself, allowing hierarchical or recursive queries (org charts, tree structures).