Report Contiguous Dates - Practice Coding Problems

Report Contiguous Dates - Problem

Database Hard

You're tasked with analyzing the historical performance of a daily task system running throughout 2019. The system executes exactly one independent task each day, which either succeeds or fails.

Given two tables:

Failed - Contains dates when tasks failed
Succeeded - Contains dates when tasks succeeded

Your goal: Generate a report showing contiguous periods of consecutive days with the same outcome (all failed or all succeeded) from 2019-01-01 to 2019-12-31.

Each period should include:

period_state: Either 'failed' or 'succeeded'
start_date: First date of the consecutive period
end_date: Last date of the consecutive period

Example: If tasks failed on Jan 1-3, succeeded on Jan 4-7, then failed on Jan 8, you'd have three periods: failed (Jan 1-3), succeeded (Jan 4-7), and failed (Jan 8-8).

Input & Output

basic_example.sql — Basic Case

$ Input: Failed table: [2019-01-01, 2019-01-02, 2019-01-03] Succeeded table: [2019-01-04, 2019-01-05]

› Output: start_date: 2019-01-01, end_date: 2019-01-03, period_state: failed start_date: 2019-01-04, end_date: 2019-01-05, period_state: succeeded

💡 Note: First 3 days failed consecutively, then 2 days succeeded consecutively, creating two distinct periods

alternating_example.sql — Alternating Status

$ Input: Failed table: [2019-01-01, 2019-01-03, 2019-01-05] Succeeded table: [2019-01-02, 2019-01-04]

› Output: start_date: 2019-01-01, end_date: 2019-01-01, period_state: failed start_date: 2019-01-02, end_date: 2019-01-02, period_state: succeeded start_date: 2019-01-03, end_date: 2019-01-03, period_state: failed start_date: 2019-01-04, end_date: 2019-01-04, period_state: succeeded start_date: 2019-01-05, end_date: 2019-01-05, period_state: failed

💡 Note: Status alternates daily, so each day forms its own single-day period

year_boundary.sql — Year Boundary

$ Input: Failed table: [2019-12-30, 2019-12-31] Succeeded table: [2019-12-29]

› Output: start_date: 2019-12-29, end_date: 2019-12-29, period_state: succeeded start_date: 2019-12-30, end_date: 2019-12-31, period_state: failed

💡 Note: Tests the year boundary constraint - only dates within 2019 are considered, forming one success period and one failure period

Visualization

Tap to expand

Understanding the Visualization

Collect All Reports

Gather all success and failure dates from 2019, like collecting daily reports from two different departments

Sort Chronologically

Arrange all reports in date order, mixing successes and failures in chronological sequence

Number Within Groups

Assign sequential numbers within each status type (1st success, 2nd success, etc.)

Calculate Grouping Key

Subtract group numbers from overall sequence - this difference stays constant within consecutive periods

Bundle Consecutive Periods

Group by the calculated key to bundle consecutive days of same status together

Key Takeaway

🎯 Key Insight: Using ROW_NUMBER() with the difference technique elegantly groups consecutive dates by exploiting the mathematical property that rank differences remain constant within contiguous sequences of the same status.

Time & Space Complexity

Time Complexity

⏱️

O(n log n)

Sorting the combined dates is the dominant operation

⚡ Linearithmic

Space Complexity

O(n)

Space to store the combined and sorted date records

⚡ Linearithmic Space

Constraints

fail_date and success_date are unique within their respective tables
All dates are within the range 2019-01-01 to 2019-12-31
The system runs exactly one task per day (no missing days in the union)
A date cannot appear in both Failed and Succeeded tables
Result must be ordered by start_date ascending

Asked in

G Google 45 a Amazon 38 ⊞ Microsoft 32 f Meta 28

The optimal solution uses window functions with row numbering to detect consecutive sequences. By calculating the difference between overall rank and status-specific rank, we can group consecutive dates with the same status. This O(n log n) approach is elegant and leverages SQL's built-in analytical capabilities effectively.

Common Approaches

Approach	Time	Space	Notes
✓ Two-Pass Status Change Detection	O(n log n)	O(n)	First pass to collect all dates, second pass to detect status changes
Window Function with Row Numbering (Optimal)	O(n log n)	O(n)	Use ROW_NUMBER() to detect consecutive sequences in a single pass
Brute Force (Date Range Scanning)	O(n³)	O(n)	Check every possible date range to identify contiguous periods

Two-Pass Status Change Detection — Algorithm Steps

First pass: Create a complete date timeline with status for each day
Handle missing dates (days with no tasks)
Second pass: Scan chronologically to detect status changes
Mark start of new period when status changes from previous day
Group consecutive days with same status into periods

Visualization

Tap to expand

Step-by-Step Walkthrough

Collect All Dates

Union both tables to get complete timeline

Detect Changes

Use LAG to compare each status with previous day

Mark Boundaries

Flag dates where status changes occur

Group Periods

Use running sum to assign group numbers

Code -

solution.c — C

#include <stdio.h>

const char* solution() {
    static const char query[] = 
    "WITH date_status_union AS (\n"
    "    SELECT fail_date as date_val, 'failed' as status\n"
    "    FROM Failed\n"
    "    WHERE fail_date BETWEEN '2019-01-01' AND '2019-12-31'\n"
    "    UNION ALL\n"
    "    SELECT success_date as date_val, 'succeeded' as status\n"
    "    FROM Succeeded\n"
    "    WHERE success_date BETWEEN '2019-01-01' AND '2019-12-31'\n"
    "),\n"
    "sequential_analysis AS (\n"
    "    SELECT date_val,\n"
    "           status,\n"
    "           LAG(status, 1) OVER (ORDER BY date_val) as prior_status\n"
    "    FROM date_status_union\n"
    "),\n"
    "transition_detection AS (\n"
    "    SELECT date_val,\n"
    "           status,\n"
    "           CASE WHEN prior_status IS NULL OR prior_status != status\n"
    "                THEN 1 ELSE 0 END as new_period_flag\n"
    "    FROM sequential_analysis\n"
    "),\n"
    "cumulative_grouping AS (\n"
    "    SELECT date_val,\n"
    "           status,\n"
    "           SUM(new_period_flag) OVER (ORDER BY date_val) as period_group\n"
    "    FROM transition_detection\n"
    ")\n"
    "SELECT MIN(date_val) as start_date,\n"
    "       MAX(date_val) as end_date,\n"
    "       status as period_state\n"
    "FROM cumulative_grouping\n"
    "GROUP BY status, period_group\n"
    "ORDER BY start_date";
    
    return query;
}

int main() {
    printf("%s\n", solution());
    return 0;
}

Time & Space Complexity

Time Complexity

⏱️

O(n log n)

Sorting the dates dominates, followed by linear scan for status changes

⚡ Linearithmic

Space Complexity

O(n)

Storage for the complete timeline of dates and statuses

⚡ Linearithmic Space

Constraints

fail_date and success_date are unique within their respective tables
All dates are within the range 2019-01-01 to 2019-12-31
The system runs exactly one task per day (no missing days in the union)
A date cannot appear in both Failed and Succeeded tables
Result must be ordered by start_date ascending

58.0K Views

High Frequency

~25 min Avg. Time

1.5K Likes

Ln 1, Col 1

Smart Actions

💡 Explanation

AI Ready

💡 Suggestion Tab to accept Esc to dismiss

// Output will appear here after running code

Code Editor Closed

Click the red button to reopen

Input & Output

Visualization

Time & Space Complexity

Related Problems

Constraints

Common Approaches

Two-Pass Status Change Detection — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Constraints

Select Compiler