# $1225. 报告系统状态的连续日期
# https://leetcode-cn.com/problems/report-contiguous-dates/

# SQL架构
Create table If Not Exists Failed (fail_date date);
Create table If Not Exists Succeeded (success_date date);
Truncate table Failed;
insert into Failed (fail_date) values ('2018-12-28');
insert into Failed (fail_date) values ('2018-12-29');
insert into Failed (fail_date) values ('2019-01-04');
insert into Failed (fail_date) values ('2019-01-05');
Truncate table Succeeded;
insert into Succeeded (success_date) values ('2018-12-30');
insert into Succeeded (success_date) values ('2018-12-31');
insert into Succeeded (success_date) values ('2019-01-01');
insert into Succeeded (success_date) values ('2019-01-02');
insert into Succeeded (success_date) values ('2019-01-03');
insert into Succeeded (success_date) values ('2019-01-06');

# Write your MySQL query statement below
select
    'failed' as period_state,
    min(fail_date) as start_date,
    max(fail_date) as end_date
from
    (
        select
            fail_date,
            subdate(fail_date, row_number() over()) as first_date
        from
            Failed
        where
            extract(
                year
                from
                    fail_date
            ) = 2019
    ) t1
group by
    first_date
union
select
    'succeeded' as period_state,
    min(success_date) as start_date,
    max(success_date) as end_date
from
    (
        select
            success_date,
            subdate(success_date, row_number() over()) as first_date
        from
            Succeeded
        where
            extract(
                year
                from
                    success_date
            ) = 2019
    ) t2
group by
    first_date
order by
    start_date;

# clean-up
drop table Failed;
drop table Succeeded;