# Report Contiguous Dates LeetCode Solution

This Leetcode problem Report Contiguous Dates LeetCode Solution is done in SQL.

# List of all LeetCode Solution

## Problem Statement

Primary key for this table is fail_date.
Failed table contains the days of failed tasks.

Primary key for this table is success_date.
Succeeded table contains the days of succeeded tasks.

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:

Output:

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)```
Scroll to Top