Welcome to Subscribe On Youtube
Formatted question description: https://leetcode.ca/all/1225.html
1225. Report Contiguous Dates
Level
Hard
Description
Table: Failed
+--------------+---------+
| Column Name | Type |
+--------------+---------+
| fail_date | date |
+--------------+---------+
Primary key for this table is fail_date.
Failed table contains the days of failed tasks.
Table: Succeeded
+--------------+---------+
| Column Name | Type |
+--------------+---------+
| success_date | date |
+--------------+---------+
Primary key for this table is success_date.
Succeeded 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 an SQL query to generate a report of 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.
Order result by start_date.
The query result format is in the following example:
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 |
+-------------------+
Result table:
+--------------+--------------+--------------+
| 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 |
+--------------+--------------+--------------+
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 system state was "failed".
From 2019-01-06 to 2019-01-06 all tasks succeeded and system state was "succeeded".
Solution
For both tables Succeeded and Failed, select the periods respectively. For each period, the start date is the date such that the previous date is not in year 2019 or not in the table, and the end date is the minimum date such that it is greater than or equal to the start date and the next date is not in year 2019 or not in the table. Use group by start_date to obtain all such ranges. Then use union all to union the results from the two tables to obtain the final result.
# Write your MySQL query statement below
select 'succeeded' as period_state, start_date, min(end_date) as end_date from (
select success_date as start_date from Succeeded
where year(success_date) = 2019 and date_sub(success_date, interval 1 day) not in (
select success_date from Succeeded where year(success_date) = 2019
)
) ss join (
select success_date as end_date from Succeeded
where year(success_date) = 2019 and date_add(success_date, interval 1 day) not in (
select success_date from Succeeded where year(success_date) = 2019
)
) se
on start_date <= end_date group by start_date
union all
select 'failed' as period_state, start_date, min(end_date) as end_date from (
select fail_date as start_date from Failed
where year(fail_date) = 2019 and date_sub(fail_date, interval 1 day) not in (
select fail_date from Failed where year(fail_date) = 2019
)
) fs join (
select fail_date as end_date from Failed
where year(fail_date) = 2019 and date_add(fail_date, interval 1 day) not in (
select fail_date from Failed where year(fail_date) = 2019
)
) fe
on start_date <= end_date group by start_date
order by start_date;