1225 Report Contiguous Dates

Description

Table: Failed

• fail_date is the primary key (column with unique values) for this table.
• This table contains the days of failed tasks.

Table: Succeeded

• success_date is the primary key (column with unique values) 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.

Problem Statement

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.

The result format is in the following example.

Example 1:

Input:

• Failed table:

• Succeeded table:

Output:

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”.

Solution

The problem needs us to group dates based on status of the task. So, you can first create a temporary view using the status of the tasks from Failed and Succeeded tables. Again, remember that you need to filter the results based on date between 2019-01-01 and 2019-12-31. You could use RANK() window function with window ordered by date column.

 1WITH ranking_per_status AS (
 2    SELECT fail_date AS status_date, 'failed' AS status,
 3        RANK() OVER (ORDER BY fail_date) AS ranking
 4        FROM Failed
 5        WHERE fail_date > '2019-01-01' AND fail_date <= '2019-12-31'
 6        UNION
 7        SELECT success_date AS status_date, 'succeeded' AS status,
 8        RANK() OVER (ORDER BY success_date) AS ranking
 9        FROM Succeeded
10        WHERE success_date >= '2019-01-01' AND success_date <= '2019-12-31'
11) SELECT * FROM ranking_per_status;

This produces below output. Notice that the output is ordered by status_date column.

The next tasks is to order these results by the status_date column and basically asigning ranking based on that ordering.

1overall_ranking AS (SELECT status_date,
2    RANK() OVER (ORDER BY status_date) AS overall_ranking,
3    status,
4    ranking,
5    (RANK() OVER (ORDER BY status_date) - ranking) AS inverse_ranking
6    FROM ranking_per_status
7) SELECT * FROM overall_ranking

This will product all rows of the previous result with ranking based on their status_date. Notice the use of inverse_ranking which is basically ranking based on date minus the previous ranking based on status and date. This way as long as the date doesn’t change, the inverse_ranking will remain the same.

Next part is to group by inverse_ranking and status and find the MIN() and MAX() of status_date.

1SELECT status AS period_state,
2    MIN(status_date) AS start_date,
3    MAX(status_date) AS end_date
4    FROM overall_ranking
5    GROUP BY inverse_ranking, status
6    ORDER BY start_date;

This produces the required result as below.

The final solution looks like this.

 1WITH ranking_per_status AS (
 2    SELECT fail_date AS status_date, 'failed' AS status,
 3        RANK() OVER (ORDER BY fail_date) AS ranking
 4        FROM Failed
 5        WHERE fail_date >= '2019-01-01' AND fail_date <= '2019-12-31'
 6        UNION
 7        SELECT success_date AS status_date, 'succeeded' AS status,
 8        RANK() OVER (ORDER BY success_date) AS ranking
 9        FROM Succeeded
10        WHERE success_date >= '2019-01-01' AND success_date <= '2019-12-31'
11), overall_ranking AS (
12    SELECT status_date,
13        RANK() OVER (ORDER BY status_date) AS overall_ranking,
14        status,
15        ranking,
16        (RANK() OVER (ORDER BY status_date) - ranking) AS inverse_ranking
17        FROM ranking_per_status
18) SELECT status AS period_state,
19    MIN(status_date) AS start_date,
20    MAX(status_date) AS end_date
21    FROM overall_ranking
22    GROUP BY inverse_ranking, status
23    ORDER BY start_date;