Report Contiguous Dates - Problem

A system runs one task every day, and each task can either fail or succeed. You have two tables tracking these outcomes:

  • Failed: Contains dates when tasks failed
  • Succeeded: Contains dates when tasks succeeded

Write a SQL query to report contiguous periods of the same outcome (failed or succeeded) for the year 2019. For each continuous interval, return:

  • period_state: Either 'failed' or 'succeeded'
  • start_date: First date of the interval
  • end_date: Last date of the interval

Return results ordered by start_date.

Table Schema

Failed
Column Name Type Description
fail_date PK date Date when task failed
Primary Key: fail_date
Succeeded
Column Name Type Description
success_date PK date Date when task succeeded
Primary Key: success_date

Input & Output

Example 1 — Mixed Success and Failure Periods
Input Tables:
Failed
fail_date
2019-01-01
2019-01-02
2019-01-05
Succeeded
success_date
2019-01-03
2019-01-04
2019-01-06
Output:
period_state start_date end_date
failed 2019-01-01 2019-01-02
succeeded 2019-01-03 2019-01-04
failed 2019-01-05 2019-01-05
succeeded 2019-01-06 2019-01-06
💡 Note:

The system shows alternating periods: 2 consecutive failed days (Jan 1-2), then 2 successful days (Jan 3-4), then single-day periods. Each contiguous sequence of the same state forms one period.
Example 2 — Long Contiguous Periods
Input Tables:
Failed
fail_date
2019-01-01
2019-01-02
2019-01-03
Succeeded
success_date
2019-01-04
2019-01-05
Output:
period_state start_date end_date
failed 2019-01-01 2019-01-03
succeeded 2019-01-04 2019-01-05
💡 Note:

Shows longer contiguous periods: a 3-day failure period followed by a 2-day success period. The window function correctly identifies these as separate groups.

Constraints

  • fail_date and success_date are within the range [2019-01-01, 2019-12-31]
  • Each date appears at most once across both tables
  • The system runs exactly one task per day

Visualization

Tap to expand
Report Contiguous Dates INPUT TABLES Failed Table fail_date 2019-01-04 2019-01-05 2019-01-06 ... Succeeded Table success_date 2019-01-01 2019-01-02 2019-01-03 ... 2019 Timeline ALGORITHM STEPS 1 UNION Both Tables Combine with state label SELECT 'failed', fail_date 2 Add Row Numbers ROW_NUMBER() per state PARTITION BY period_state 3 Compute Group Key date - row_num = grp 01-04 - 1 = 01-03 01-05 - 2 = 01-03 (same!) 4 Group and Aggregate MIN/MAX dates per group GROUP BY state, grp MIN(date), MAX(date) Gap Detection Magic Contiguous dates share grp FINAL RESULT state start end succeeded 01-01 01-03 failed 01-04 01-06 succeeded 01-07 01-10 failed 01-11 01-12 succeeded 01-13 01-31 OK Output Summary Contiguous periods merged Ordered by start_date 2019 data only Key Insight: The "date minus row_number" trick creates identical group keys for contiguous dates. For consecutive dates: (Jan 4 - 1) = (Jan 5 - 2) = (Jan 6 - 3) = Jan 3 (same group key!) This window function pattern elegantly identifies islands of contiguous records in SQL. TutorialsPoint - Report Contiguous Dates | Optimal Solution

Related Problems

Asked in
Microsoft 28 Amazon 22 Google 15
34.5K Views
Medium Frequency
~25 min Avg. Time
892 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen