Annual Service Stability Period Report

HARD30 pts

A reliability platform stores daily service health outcomes in two tables.

Tables:

outages(down_date)
recoveries(up_date)

Column details:

down_date: calendar date when the service status for that day was outage
up_date: calendar date when the service status for that day was healthy

Reporting window:

Analyze only dates from 2020-01-01 through 2020-12-31 (inclusive).
Rows outside this date range may exist and must be ignored.

Guarantee within the reporting window:

Each day appears in exactly one of the two tables.
Therefore, each day has exactly one status: outage or healthy.

Task:

Build contiguous status intervals inside the reporting window.
An interval is maximal: it cannot be extended by one more day on either side with the same status.
Return one row per interval with:
1. period_state ("outage" or "healthy")
2. start_date
3. end_date

Output requirements:

Sort by start_date in ascending order.
Return exactly the columns: period_state, start_date, end_date.

Supported submission environments:

SQL (SQLite 3.27.2)
Pandas (Python)

Example

Input

outages:
| down_date   |
|-------------|
| 2019-12-30  |
| 2020-01-04  |
| 2020-01-05  |

recoveries:
| up_date     |
|-------------|
| 2019-12-31  |
| 2020-01-01  |
| 2020-01-02  |
| 2020-01-03  |
| 2020-01-06  |

Output

[
  {"period_state":"healthy","start_date":"2020-01-01","end_date":"2020-01-03"},
  {"period_state":"outage","start_date":"2020-01-04","end_date":"2020-01-05"},
  {"period_state":"healthy","start_date":"2020-01-06","end_date":"2020-01-06"}
]

Explanation

Rows from 2019 are ignored. Inside the reporting window, days are grouped into maximal same-state streaks.

Example

Input

outages:
| down_date   |
|-------------|
| 2020-02-29  |

recoveries:
| up_date     |
|-------------|
| 2020-02-28  |
| 2020-03-01  |

Output

[
  {"period_state":"healthy","start_date":"2020-02-28","end_date":"2020-02-28"},
  {"period_state":"outage","start_date":"2020-02-29","end_date":"2020-02-29"},
  {"period_state":"healthy","start_date":"2020-03-01","end_date":"2020-03-01"}
]

Explanation

Leap day is treated like any normal calendar day. Status flips create separate intervals.

Example

Input

outages:
| down_date   |
|-------------|
| 2020-04-10  |
| 2020-04-11  |
| 2020-04-12  |

recoveries:
| up_date     |
|-------------|
| 2020-04-01  |
| 2020-04-02  |
| 2020-04-03  |
| 2020-04-04  |
| 2020-04-05  |
| 2020-04-06  |
| 2020-04-07  |
| 2020-04-08  |
| 2020-04-09  |
| 2020-04-13  |
| 2020-04-14  |

Output

[
  {"period_state":"healthy","start_date":"2020-04-01","end_date":"2020-04-09"},
  {"period_state":"outage","start_date":"2020-04-10","end_date":"2020-04-12"},
  {"period_state":"healthy","start_date":"2020-04-13","end_date":"2020-04-14"}
]

Explanation

Three consecutive outage days form one outage interval between two healthy intervals.

Accepted0/0·0% Acceptance

Constraints

0 <= number of rows in outages <= 200000
0 <= number of rows in recoveries <= 200000
down_date values are unique in outages
up_date values are unique in recoveries
Date strings are valid and use YYYY-MM-DD format
Within 2020-01-01..2020-12-31, every day appears in exactly one table
Rows outside 2020-01-01..2020-12-31 can exist and must be ignored
Return exactly these columns: period_state, start_date, end_date
Order output by start_date ascending
Use SQLite 3.27.2 semantics for SQL submissions
For Pandas submissions, return a DataFrame with columns: period_state, start_date, end_date

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

outages =

[{"down_date":"2022-02-20"},{"down_date":"2020-01-05"},{"down_date":"2019-10-26"},{"down_date":"2021-02-10"},{"down_date":"2022-12-19"},{"down_date":"2022-08-27"},{"down_date":"2020-01-04"}]

recoveries =

Annual Service Stability Period Report

Hints

Annual Service Stability Period Report

Hints