Report Contiguous Dates

Report Contiguous Dates

What is this problem about?

The Report Contiguous Dates interview question is a complex SQL problem involving two tables: one recording days when the system was failing and another recording days when it was succeeding. You must find all contiguous date intervals of consecutive failing days and consecutive succeeding days, then combine them into a single result ordered by start date. This requires identifying consecutive date groups and building interval summaries from them.

Why is this asked in interviews?

Meta and Amazon include this HARD database problem in interviews because it tests advanced SQL skills: date arithmetic, grouping consecutive dates using the "islands and gaps" technique, UNION operations, and ordering. It mirrors real-world monitoring dashboards where engineers need to summarize system health periods — critical in SRE, DevOps, and data analytics roles.

Algorithmic pattern used

The pattern is the "islands and gaps" technique for consecutive date grouping. For each table (Failed, Succeeded), assign a group key by subtracting a dense row number from the date — dates that are consecutive will share the same group key. Then GROUP BY this key and aggregate to find the MIN (start date) and MAX (end date) of each contiguous island. UNION ALL both results, add a state column, and ORDER BY start_date.

WITH failed_groups AS (
  SELECT fail_date, DATE_SUB(fail_date, INTERVAL ROW_NUMBER() OVER (ORDER BY fail_date) DAY) AS grp
  FROM Failed WHERE fail_date BETWEEN '2019-01-01' AND '2019-12-31'
),
succeeded_groups AS (
  SELECT success_date, DATE_SUB(success_date, INTERVAL ROW_NUMBER() OVER (ORDER BY success_date) DAY) AS grp
  FROM Succeeded WHERE success_date BETWEEN '2019-01-01' AND '2019-12-31'
)
SELECT 'failed' AS period_state, MIN(fail_date) AS start_date, MAX(fail_date) AS end_date FROM failed_groups GROUP BY grp
UNION ALL
SELECT 'succeeded', MIN(success_date), MAX(success_date) FROM succeeded_groups GROUP BY grp
ORDER BY start_date;

Example explanation

Failed dates: Jan 2, Jan 3, Jan 4 (consecutive → one group: Jan 2 – Jan 4). Succeeded dates: Jan 5, Jan 6 (consecutive → one group: Jan 5 – Jan 6), Jan 10 (gap → separate group).

Result:

period_state	start_date	end_date
failed	2019-01-02	2019-01-04
succeeded	2019-01-05	2019-01-06
succeeded	2019-01-10	2019-01-10

Common mistakes candidates make

• Not filtering dates to the required year range before applying the islands technique.
• Confusing ROW_NUMBER() with RANK() — ROW_NUMBER() gives sequential integers needed for the group key.
• Forgetting the UNION ALL (not UNION) — duplicates are not possible across tables so ALL is safe and more efficient.
• Not ordering the final result by start_date.

Interview preparation tip

For the Report Contiguous Dates coding problem, the database interview pattern relies on the "islands and gaps" technique — one of the most powerful SQL idioms for time-series analysis. Practice applying ROW_NUMBER() and date subtraction to group consecutive dates. Interviewers at Meta appreciate when you explain the mathematical insight: consecutive dates produce the same (date - row_number) value, which is the group key.