Report Contiguous Dates - Solution & Explanation

HardPremiumFree on FleetCodeDatabase5 min readAsked at: Amazon, Meta

Problem Statement

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".

Approach Overview

Problem Overview: You receive two tables: Succeeded and Failed, each storing dates. The task is to report continuous date ranges in 2019 where the system status stayed the same (either succeeded or failed). Each output row represents a contiguous interval with start_date, end_date, and the corresponding state.

Approach 1: Union + Window Function + Group By (O(n log n) time, O(n) space)

Start by combining both tables into a single dataset using UNION ALL, while attaching a status label ('succeeded' or 'failed'). Filter rows so only dates within 2019 remain. Once merged, sort by date and apply the window function ROW_NUMBER(). The key insight is the classic "gaps and islands" trick: subtract the row number from the date to generate a constant key for consecutive rows belonging to the same state. Rows within the same contiguous sequence produce the same derived value. Group by the state and this derived key, then compute MIN(date) and MAX(date) to form each interval. This approach relies heavily on SQL analytical features and is the most compact way to solve the problem in MySQL.

Approach 2: Window Boundary Detection with LAG (O(n log n) time, O(n) space)

Another way to detect contiguous segments is by comparing each row with the previous row using LAG(). After combining the two tables with UNION ALL, order rows by date and compute the previous date and previous state. A new segment begins when either the state changes or the date is not exactly one day after the previous date. Mark these boundary rows and use a cumulative sum window to assign a group id to each segment. Finally, aggregate each group to produce the start and end dates. This approach makes the segment boundaries explicit and is useful when working with more complex sequence logic using window functions in database queries.

Recommended for interviews: The union + row number grouping method is the expected solution. It demonstrates familiarity with the "gaps and islands" pattern, which frequently appears in SQL interviews. A candidate who explains why date - row_number() creates stable groups for consecutive records shows strong analytical SQL skills.

Solution

We can merge the two tables into one table with a field st representing the status, where failed indicates failure and succeeded indicates success. Then, we can use a window function to group the records with the same status into one group, and calculate the difference between each date and its rank within the group as pt, which serves as the identifier for the same continuous status. Finally, we can group by st and pt, and calculate the minimum and maximum dates for each group, and sort by the minimum date.

Code

MySQL

Try this approach in the editor →

Detailed Complexity Analysis

Approach	Time	Space	When to Use
Union + ROW_NUMBER() Grouping	O(n log n)	O(n)	Best general solution for contiguous date problems using the gaps-and-islands technique
LAG Boundary Detection + Window Grouping	O(n log n)	O(n)	Useful when explicitly detecting state changes or irregular gaps between rows

Video Solution

Leetcode Hard 1225: Find Continuous Dates META Advanced SQL Data Science Interview Question Solved! • Everyday Data Science • 3,201 views views

Watch 2 more video solutions →

Frequently Asked Questions

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.

Ready to solve this problem?

Practice Report Contiguous Dates with our built-in code editor and test cases.

Practice on FleetCode

Customers With Strictly Increasing Purchases

Find the Missing IDs

Problem Info

DifficultyHard

Acceptance56.9%

Approaches1

Reading time5 min

Asked at

Amazon Meta

Practice this problem

Open in Editor