题目地址

https://leetcode.cn/problems/report-contiguous-dates/description/

题目描述

A system is running one task every day. Every task is independent of the previous tasks. The tasks can fail or succeed.

Write a solution to report the 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.

Return the result table ordered by start_date.

代码

# Write your MySQL query statement below

with t1 as(
    select "failed" as status, fail_date as task_date from Failed 
    union all
    select "succeeded" as status, success_date as task_date from Succeeded
)
,
t2 as (
    select * from t1 
    where task_date between date("2019-01-01") and date("2019-12-31")
    order by task_date asc
)
,
t3 as (
    select *, 
    lag(task_date, 1, task_date -interval 2 day) over(order by task_date) as lag_task_date,
    lag(status, 1) over(order by task_date) as lag_status,
    lead(task_date, 1, task_date + interval 2 day) over(order by task_date) as lead_task_date,
    lead(status, 1) over(order by task_date) as lead_status
    from t2
)
,
Segments as(
    select *,
    (case when datediff(task_date, lag_task_date) >1 or status<>lag_status then 1 else 0 end) as is_start ,
    (case when datediff(lead_task_date, task_date)>1 or status<>lead_status then 1 else 0 end) as is_end
    from t3
)
,
GroupsWithNumber AS (
    SELECT *,
           SUM(is_start) OVER (ORDER BY task_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS group_num
    FROM Segments
)
,
comprehensive_statistic as(
    SELECT *,
    COUNT(task_date) OVER (PARTITION BY group_num) AS segment_length
    FROM GroupsWithNumber
    ORDER BY task_date
)

# select * from comprehensive_statistic

select period_state , start_date  , end_date from
(select status as period_state, group_num, min(case when is_start then task_date end) as start_date,
max(case when is_end then task_date end) as end_date
from comprehensive_statistic
group by status,group_num) tmp1
order by start_date asc