Annual Service Stability Period Report

Hard

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: period_state ("outage" or "healthy") start_date 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 1

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 2

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 3

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.

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

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 |

[ {"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"} ]

[ {"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"} ]

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 |

[ {"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"} ]