Skip to content

Latest commit

 

History

History
138 lines (118 loc) · 3.85 KB

README_EN.md

File metadata and controls

138 lines (118 loc) · 3.85 KB

1225. Report Contiguous Dates

中文文档

Description

Table: Failed

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| fail_date    | date    |
+--------------+---------+
fail_date is the primary key for this table.
This table contains the days of failed tasks.

 

Table: Succeeded

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| success_date | date    |
+--------------+---------+
success_date is the primary key for this table.
This table contains the days of succeeded tasks.

 

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

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.

Return the result table ordered by start_date.

The query result format is in the following example.

 

Example 1:

Input: 
Failed table:
+-------------------+
| fail_date         |
+-------------------+
| 2018-12-28        |
| 2018-12-29        |
| 2019-01-04        |
| 2019-01-05        |
+-------------------+
Succeeded table:
+-------------------+
| success_date      |
+-------------------+
| 2018-12-30        |
| 2018-12-31        |
| 2019-01-01        |
| 2019-01-02        |
| 2019-01-03        |
| 2019-01-06        |
+-------------------+
Output: 
+--------------+--------------+--------------+
| period_state | start_date   | end_date     |
+--------------+--------------+--------------+
| succeeded    | 2019-01-01   | 2019-01-03   |
| failed       | 2019-01-04   | 2019-01-05   |
| succeeded    | 2019-01-06   | 2019-01-06   |
+--------------+--------------+--------------+
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 the system state was "failed".
From 2019-01-06 to 2019-01-06 all tasks succeeded and the system state was "succeeded".

Solutions

SQL

# Write your MySQL query statement below
select
    state as period_state,
    min(dt) as start_date,
    max(dt) as end_date
from
    (
        select
            *,
            subdate(
                dt,
                rank() over(
                    partition by state
                    order by
                        dt
                )
            ) as dif
        from
            (
                select
                    'failed' as state,
                    fail_date as dt
                from
                    failed
                where
                    fail_date between '2019-01-01'
                    and '2019-12-31'
                union
                all
                select
                    'succeeded' as state,
                    success_date as dt
                from
                    succeeded
                where
                    success_date between '2019-01-01'
                    and '2019-12-31'
            ) t1
    ) t2
group by
    state,
    dif
order by
    dt