Is Report Contiguous Dates easy or hard?

Report Contiguous Dates is considered a hard SQL problem because it requires understanding the gaps-and-islands pattern and using window functions correctly. Developers unfamiliar with analytical SQL functions often struggle to identify contiguous sequences efficiently.

Report Contiguous Dates Python/Java solution

This problem is designed for SQL rather than general-purpose languages. In Python or Java, you would merge both date lists, sort them, and iterate through the ordered sequence to build contiguous intervals whenever the state changes or the date gap exceeds one day.

How to solve Report Contiguous Dates in O(n)?

Pure O(n) is difficult in SQL because ordering by date is required to detect contiguous sequences. Practical solutions rely on sorting followed by window functions such as ROW_NUMBER or LAG. After sorting, grouping contiguous records becomes straightforward using the gaps-and-islands pattern.

What is the best approach for Report Contiguous Dates?

The most common solution uses UNION ALL to combine the Succeeded and Failed tables, then applies ROW_NUMBER() to detect contiguous sequences. By subtracting the row number from the date, consecutive rows form stable groups that can be aggregated with GROUP BY. This gaps-and-islands technique is widely used for SQL sequence problems.

Is Report Contiguous Dates asked at Google/Amazon/Meta?

Contiguous interval problems and gaps-and-islands SQL questions appear frequently in interviews at companies like Amazon, Meta, and Google. While this exact problem may vary, the underlying concept of detecting consecutive sequences in SQL tables is commonly tested.

What data structure is used in Report Contiguous Dates?

The problem primarily relies on relational database operations rather than traditional data structures. Window functions such as ROW_NUMBER and LAG, along with GROUP BY aggregation, are used to identify contiguous ranges within ordered datasets.

What is the time complexity of Report Contiguous Dates?

The SQL solution typically runs in O(n log n) time because the dataset must be ordered by date before applying window functions like ROW_NUMBER or LAG. The grouping and aggregation operations are linear after sorting. Space complexity is O(n) due to intermediate result sets.

1225. Report Contiguous Dates - Practice Online

Table: Failed

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| fail_date    | date    |
+--------------+---------+
fail_date is the primary key (column with unique values) for this table.
This table contains the days of failed tasks.

Table: Succeeded

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| success_date | date    |
+--------------+---------+
success_date is the primary key (column with unique values) for this table.
This table contains the days of succeeded tasks.

A system is running one task every day. Every task is independent of the previous tasks. The tasks can fail or succeed.

Write a solution to report the period_state for each continuous interval of days in the period from 2019-01-01 to 2019-12-31.

period_state is 'failed' if tasks in this interval failed or 'succeeded' if tasks in this interval succeeded. Interval of days are retrieved as start_date and end_date.

Return the result table ordered by start_date.

The result format is in the following example.

Example 1:

Input: 
Failed table:
+-------------------+
| fail_date         |
+-------------------+
| 2018-12-28        |
| 2018-12-29        |
| 2019-01-04        |
| 2019-01-05        |
+-------------------+
Succeeded table:
+-------------------+
| success_date      |
+-------------------+
| 2018-12-30        |
| 2018-12-31        |
| 2019-01-01        |
| 2019-01-02        |
| 2019-01-03        |
| 2019-01-06        |
+-------------------+
Output: 
+--------------+--------------+--------------+
| period_state | start_date   | end_date     |
+--------------+--------------+--------------+
| succeeded    | 2019-01-01   | 2019-01-03   |
| failed       | 2019-01-04   | 2019-01-05   |
| succeeded    | 2019-01-06   | 2019-01-06   |
+--------------+--------------+--------------+
Explanation: 
The report ignored the system state in 2018 as we care about the system in the period 2019-01-01 to 2019-12-31.
From 2019-01-01 to 2019-01-03 all tasks succeeded and the system state was "succeeded".
From 2019-01-04 to 2019-01-05 all tasks failed and the system state was "failed".
From 2019-01-06 to 2019-01-06 all tasks succeeded and the system state was "succeeded".