Gap Detection — Missing Days / Sequences

-- Find days within the last 30 with ZERO orders
WITH days AS (
    SELECT day::date
    FROM   generate_series(CURRENT_DATE - 29, CURRENT_DATE, INTERVAL '1 day') AS day
)
SELECT d.day
FROM   days d
LEFT   JOIN orders o ON DATE(o.created_at) = d.day
WHERE  o.id IS NULL
ORDER  BY d.day;

-- Missing values in a numeric sequence (e.g., invoice numbers)
WITH ranges AS (
    SELECT id,
           LAG(id) OVER (ORDER BY id) AS prev_id
    FROM   invoices
)
SELECT prev_id + 1 AS gap_start,
       id - 1       AS gap_end
FROM   ranges
WHERE  id > prev_id + 1;

-- "Run length" — consecutive same-value streaks (islands and gaps trick)
SELECT min(day) AS run_start, max(day) AS run_end, status, count(*) AS days
FROM (
    SELECT day, status,
           day - (ROW_NUMBER() OVER (PARTITION BY status ORDER BY day))::int AS grp
    FROM   daily_status
) t
GROUP BY status, grp ORDER BY run_start;