好题分享、心路历程（力扣1225）——连续登录

【题目介绍】

该题为力扣1225，名为报告系统状态的连续日期。

【题型分类】

属于连续专题。

官网标为困难题。

【思路分享】

这里的连续属于时间连续，采用 row_number()、subdate() 技巧解题。

关键点：

1. 连续序列：需构造连续序列，以 row_number() 排序标号，记为 rnk。

2. 不连续序列：已有不连续时间列，以日期标号。

3. 连续序列与不连续序列差值：需构造序列差值，以 subdate() 做差；差值相同的为连续状态，以便后续分组；记为 grp。

题解：

with tmp1 as
(select *,
    row_number() over(order by fail_date) as rnk,
    subdate(fail_date,row_number() over(order by fail_date)) as grp
from Failed 
where fail_date between '2019-01-01' and '2019-12-31')

,tmp2 as
(select *,
    row_number() over(order by success_date) as rnk,
    subdate(success_date,row_number() over(order by success_date)) as grp
from Succeeded 
where success_date between '2019-01-01' and '2019-12-31')

,tmp3 as
((select distinct 'failed' as period_state,
    min(fail_date) as start_date,
    max(fail_date) as end_date
from tmp1
group by grp)
union all
(select distinct 'succeeded' as period_state,
    min(success_date) as start_date,
    max(success_date) as end_date
from tmp2
group by grp))

select *
from tmp3
order by start_date

-END