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 ProblemInput: Two Date TablesFailedfail_date2019-01-012019-01-02Succeededsuccess_date2019-01-032019-01-04UNION ALL+ Window FnCombined Timelinedatestate01-01failed01-02failed01-03succeeded01-04succeededGROUP BYContiguousOutput: Periodsstatestartendfailed01-0101-02succeeded01-0301-04Each contiguous sequence of same state becomes one period
Understanding the Visualization
1
Input Tables
Failed and Succeeded date tables
2
Union & Group
Combine tables and identify contiguous periods
3
Output
Period states with start/end dates
Key Takeaway
🎯 Key Insight: Use ROW_NUMBER() arithmetic to detect group boundaries in sequential data

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