Last updated on January 6th, 2025 at 10:04 pm

This Leetcode problem Report Contiguous Dates LeetCode Solution is done in SQL.

List of all LeetCode Solution

Report Contiguous Dates LeetCode Solution

Problem Statement

Primary key for this table is fail_date.
Failed table contains the days of failed tasks.

Primary key for this table is success_date.
Succeeded 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.
Order result by start_date.

The result format is in the following example.

Example 1:
Input:

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 system state was “failed”.
From 2019-01-06 to 2019-01-06 all tasks succeeded and system state was “succeeded”.

1. Report Contiguous Dates LeetCode Solution MySQL

select 
  period_state, 
  start_date, 
  end_date 
from 
  (
    select 
      'failed' as period_state, 
      f1.fail_date as start_date, 
      f2.fail_date as end_date 
    from 
      (
        select 
          fail_date 
        from 
          Failed 
        where 
          fail_date between '2019-01-01' 
          and '2019-12-31' 
          and date_sub(fail_date, interval 1 day) not in (
            select 
              * 
            from 
              Failed 
            where 
              fail_date between '2019-01-01' 
              and '2019-12-31'
          )
      ) as f1, 
      (
        select 
          fail_date 
        from 
          Failed 
        where 
          fail_date between '2019-01-01' 
          and '2019-12-31' 
          and date_add(fail_date, interval 1 day) not in (
            select 
              * 
            from 
              Failed 
            where 
              fail_date between '2019-01-01' 
              and '2019-12-31'
          )
      ) as f2 
    where 
      f1.fail_date <= f2.fail_date 
    group by 
      f1.fail_date 
    union 
    select 
      'succeeded' as period_state, 
      s1.success_date as start_date, 
      s2.success_date as end_date 
    from 
      (
        select 
          success_date 
        from 
          Succeeded 
        where 
          success_date between '2019-01-01' 
          and '2019-12-31' 
          and date_sub(success_date, interval 1 day) not in (
            select 
              * 
            from 
              Succeeded 
            where 
              success_date between '2019-01-01' 
              and '2019-12-31'
          )
      ) as s1, 
      (
        select 
          success_date 
        from 
          Succeeded 
        where 
          success_date between '2019-01-01' 
          and '2019-12-31' 
          and date_add(success_date, interval 1 day) not in (
            select 
              * 
            from 
              Succeeded 
            where 
              success_date between '2019-01-01' 
              and '2019-12-31'
          )
      ) as s2 
    where 
      s1.success_date <= s2.success_date 
    group by 
      s1.success_date
  ) as p 
order by 
  start_date;