Merge Overlapping Events in the Same Hall

Merge Overlapping Events in the Same Hall - Problem

Database Hard

You're managing a venue booking system for a conference center with multiple halls. Each hall can host events, and sometimes bookings overlap or are scheduled back-to-back. Your task is to merge all overlapping events within the same hall to get a clear picture of actual venue usage.

Given a table HallEvents with columns:

hall_id (int): The hall identifier
start_day (date): Event start date
end_day (date): Event end date

Two events overlap if they have at least one day in common. For example, an event from Jan 1-3 overlaps with an event from Jan 3-5 (they share Jan 3).

Your goal: Write a SQL solution to merge all overlapping events in the same hall and return the consolidated booking periods.

Note: The table may contain duplicate rows, and events are only merged if they're in the same hall.

Input & Output

example_1.sql — Basic Overlap

$ Input: HallEvents table: +----------+------------+----------+ | hall_id | start_day | end_day | +----------+------------+----------+ | 1 | 2023-01-13 | 2023-01-15 | | 1 | 2023-01-14 | 2023-01-17 | | 1 | 2023-01-18 | 2023-01-25 | | 2 | 2023-01-10 | 2023-01-20 | | 2 | 2023-01-15 | 2023-01-17 | +----------+------------+----------+

› Output: +----------+------------+----------+ | hall_id | start_day | end_day | +----------+------------+----------+ | 1 | 2023-01-13 | 2023-01-17 | | 1 | 2023-01-18 | 2023-01-25 | | 2 | 2023-01-10 | 2023-01-20 | +----------+------------+----------+

💡 Note: In hall 1: events Jan 13-15 and Jan 14-17 overlap (share Jan 14-15), so they merge to Jan 13-17. Event Jan 18-25 is separate. In hall 2: event Jan 15-17 is completely contained within Jan 10-20, so they merge to Jan 10-20.

example_2.sql — Adjacent Events

$ Input: HallEvents table: +----------+------------+----------+ | hall_id | start_day | end_day | +----------+------------+----------+ | 1 | 2023-01-01 | 2023-01-03 | | 1 | 2023-01-03 | 2023-01-05 | | 1 | 2023-01-06 | 2023-01-08 | +----------+------------+----------+

› Output: +----------+------------+----------+ | hall_id | start_day | end_day | +----------+------------+----------+ | 1 | 2023-01-01 | 2023-01-05 | | 1 | 2023-01-06 | 2023-01-08 | +----------+------------+----------+

💡 Note: Events Jan 1-3 and Jan 3-5 overlap on Jan 3, so they merge to Jan 1-5. Event Jan 6-8 doesn't overlap with the merged event (no shared days), so it remains separate.

example_3.sql — Duplicate Events

$ Input: HallEvents table: +----------+------------+----------+ | hall_id | start_day | end_day | +----------+------------+----------+ | 1 | 2023-01-01 | 2023-01-03 | | 1 | 2023-01-01 | 2023-01-03 | | 1 | 2023-01-02 | 2023-01-04 | +----------+------------+----------+

› Output: +----------+------------+----------+ | hall_id | start_day | end_day | +----------+------------+----------+ | 1 | 2023-01-01 | 2023-01-04 | +----------+------------+----------+

💡 Note: All three events (including duplicates) overlap and merge into a single period from Jan 1-4. The duplicate Jan 1-3 events are handled naturally by the DISTINCT clause.

Constraints

1 ≤ Number of rows ≤ 10⁴
1 ≤ hall_id ≤ 100
start_day ≤ end_day
Table may contain duplicate rows
Events overlap if they share at least one day

Visualization

Tap to expand

Understanding the Visualization

Sort by Hall & Start

Organize events chronologically within each hall

Detect Gaps

Find where current event starts after previous event ends

Create Islands

Group consecutive overlapping events together

Merge Islands

Combine each island into a single time period

Key Takeaway

🎯 Key Insight: The islands and gaps technique efficiently groups overlapping intervals using window functions, avoiding the need for expensive self-joins.

Asked in

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

The optimal solution uses the islands and gaps technique with window functions. Key insight: overlapping events form 'islands' that can be identified by comparing each event's start date with the previous event's end date. Use LAG() and SUM() window functions to group overlapping events, then merge each group with MIN(start_day) and MAX(end_day).

Common Approaches

Approach	Time	Space	Notes
✓ Islands and Gaps (Optimal)	O(n log n)	O(n)	Use window functions to identify overlapping event groups (islands)
Brute Force (Self Join)	O(n²)	O(n²)	Join table with itself to find all overlapping pairs, then aggregate

Islands and Gaps (Optimal) — Algorithm Steps

Remove duplicate events first
Sort events by hall_id and start_day
Use window functions to identify 'islands' of overlapping events
Create island groups using cumulative sum technique
Merge events within each island group

Visualization

Tap to expand

Step-by-Step Walkthrough

Sort Events

Order by hall_id, then start_day

Find Islands

Mark boundaries where events don't overlap

Group Islands

Assign same island_id to overlapping events

Merge Groups

Take MIN(start_day), MAX(end_day) per group

Code -

solution.c — C

// SQL solution using islands and gaps technique
char* sqlQuery = "\
WITH distinct_events AS ( \
    SELECT DISTINCT hall_id, start_day, end_day \
    FROM HallEvents \
), \
ordered_events AS ( \
    SELECT  \
        hall_id, start_day, end_day, \
        LAG(end_day) OVER (PARTITION BY hall_id ORDER BY start_day) as prev_end \
    FROM distinct_events \
), \
island_starts AS ( \
    SELECT  \
        hall_id, start_day, end_day, \
        CASE  \
            WHEN prev_end IS NULL OR start_day > prev_end  \
            THEN 1 ELSE 0  \
        END as is_start \
    FROM ordered_events \
), \
island_groups AS ( \
    SELECT  \
        hall_id, start_day, end_day, \
        SUM(is_start) OVER ( \
            PARTITION BY hall_id  \
            ORDER BY start_day  \
            ROWS UNBOUNDED PRECEDING \
        ) as island_id \
    FROM island_starts \
) \
SELECT  \
    hall_id, \
    MIN(start_day) as start_day, \
    MAX(end_day) as end_day \
FROM island_groups \
GROUP BY hall_id, island_id \
ORDER BY hall_id, start_day;"

Time & Space Complexity

Time Complexity

⏱️

O(n log n)

Dominated by sorting operations in window functions

⚡ Linearithmic

Space Complexity

O(n)

Intermediate results stored in CTEs

⚡ Linearithmic Space

52.0K Views

High Frequency

~25 min Avg. Time

1.8K 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

Constraints

Visualization

Related Problems

Common Approaches

Islands and Gaps (Optimal) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler