Leetcode 1225. 报告系统状态的连续日期

题目地址：https://leetcode-cn.com/problems/report-contiguous-dates/

select a.period_state,a.start_date,a.end_date
from(
select a.diff,'succeeded' period_state ,min(a.success_date)start_date,max(a.success_date)end_date
from(
select 
(unix_timestamp(success_date)-(select unix_timestamp(min(success_date)) from Succeeded where year(success_date)=2019))/86400
-row_number() over(order by success_date) diff
,success_date
from Succeeded
where year(success_date)=2019
)a group by diff 
union 
select a.diff,'failed' period_state,min(a.fail_date)start_date,max(a.fail_date)end_date
from(
select 
(unix_timestamp(fail_date)-(select unix_timestamp(min(fail_date)) from Failed where year(fail_date)=2019))/86400
-row_number() over(order by fail_date) diff 
,fail_date
from Failed
where year(fail_date)=2019
)a group by diff 
)a 
order by a.start_date,a.end_date