Leetcode Problem 1225. Report Contiguous Dates

1225. Report Contiguous Dates

Leetcode Solutions

Using Window Functions to Identify Continuous Date Intervals

Combine the 'Failed' and 'Succeeded' tables into a single table with an additional column to indicate the state ('failed' or 'succeeded').
Use the ROW_NUMBER() window function to assign a sequential integer to each date, ordered by the date.
Use the DENSE_RANK() window function to assign a rank to each date, ordered by the date, within each state.
Calculate the difference between the row number and the rank for each date to create a unique group identifier for continuous date intervals.
Group by the state and the calculated difference to find the minimum and maximum dates within each group, which represent the start and end dates of each interval.
Order the final result by the start date.

erDiagram
    FAILED ||--|| FAILED : fail_date
    SUCCEEDED ||--|| SUCCEEDED : success_date
    FAILED {
        date fail_date PK
    }
    SUCCEEDED {
        date success_date PK
    }

Using Self-Join to Identify Continuous Date Intervals

Ask Question

Programming Language

Purpose:

General Question

Debug My Code

image/screenshot of info(optional)

Full Screen

Leetcode Problem 1225. Report Contiguous Dates

Leetcode Solutions

Using Window Functions to Identify Continuous Date Intervals

Solution Idea

Query Steps

Code Implementation

Using Self-Join to Identify Continuous Date Intervals

Ask Question

Suggested Answer