This Leetcode problem Report Contiguous Dates LeetCode Solution is done in SQL.
List of all LeetCode Solution
![Report Contiguous Dates LeetCode Solution](https://i0.wp.com/totheinnovation.com/wp-content/uploads/2024/02/LeetCode-Problem-Solution.png?resize=200%2C200&ssl=1)
Report Contiguous Dates LeetCode Solution
Table of Contents
Problem Statement
Column Name | Type |
fail_date | date |
Primary key for this table is fail_date.
Failed table contains the days of failed tasks.
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 result format is in the following example.
Example 1:
Input:
fail_date |
2018-12-28 |
2018-12-29 |
2019-01-04 |
2019-01-05 |
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 system state was “failed”.
From 2019-01-06 to 2019-01-06 all tasks succeeded and system state was “succeeded”.
Report Contiguous Dates LeetCode Solution MySQL
select
period_state,
start_date,
end_date
from
(
select
'failed' as period_state,
f1.fail_date as start_date,
f2.fail_date as end_date
from
(
select
fail_date
from
Failed
where
fail_date between '2019-01-01'
and '2019-12-31'
and date_sub(fail_date, interval 1 day) not in (
select
*
from
Failed
where
fail_date between '2019-01-01'
and '2019-12-31'
)
) as f1,
(
select
fail_date
from
Failed
where
fail_date between '2019-01-01'
and '2019-12-31'
and date_add(fail_date, interval 1 day) not in (
select
*
from
Failed
where
fail_date between '2019-01-01'
and '2019-12-31'
)
) as f2
where
f1.fail_date <= f2.fail_date
group by
f1.fail_date
union
select
'succeeded' as period_state,
s1.success_date as start_date,
s2.success_date as end_date
from
(
select
success_date
from
Succeeded
where
success_date between '2019-01-01'
and '2019-12-31'
and date_sub(success_date, interval 1 day) not in (
select
*
from
Succeeded
where
success_date between '2019-01-01'
and '2019-12-31'
)
) as s1,
(
select
success_date
from
Succeeded
where
success_date between '2019-01-01'
and '2019-12-31'
and date_add(success_date, interval 1 day) not in (
select
*
from
Succeeded
where
success_date between '2019-01-01'
and '2019-12-31'
)
) as s2
where
s1.success_date <= s2.success_date
group by
s1.success_date
) as p
order by
start_date;
Code language: SQL (Structured Query Language) (sql)