Hall Event Overlap Consolidation

Hard

A venue operations platform stores booked date windows for events in different halls. Scheduling analysts need a cleaned timeline where overlapping bookings are consolidated so downstream utilization and conflict reporting are accurate. Table: HallEvents hall_id: hall identifier start_day: first day of the event window end_day: last day of the event window Notes: Input rows can contain duplicates. Merging must be done independently for each hall. Intervals are inclusive on both ends. Task: Return consolidated event windows per hall after merging every overlapping interval. Two intervals overlap when they share at least one calendar day. Output requirements: Return exactly three columns: hall_id, start_day, end_day Output row order is not important Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

HallEvents:
| hall_id | start_day  | end_day    |
|---------|------------|------------|
| 10      | 2024-01-02 | 2024-01-04 |
| 10      | 2024-01-04 | 2024-01-08 |
| 10      | 2024-01-12 | 2024-01-14 |
| 20      | 2024-02-05 | 2024-02-15 |
| 20      | 2024-02-07 | 2024-02-09 |
| 30      | 2024-03-01 | 2024-03-01 |

Output

[
  {"hall_id":10,"start_day":"2024-01-02","end_day":"2024-01-08"},
  {"hall_id":10,"start_day":"2024-01-12","end_day":"2024-01-14"},
  {"hall_id":20,"start_day":"2024-02-05","end_day":"2024-02-15"},
  {"hall_id":30,"start_day":"2024-03-01","end_day":"2024-03-01"}
]

Explanation

Hall 10 has two overlapping rows that merge into one interval plus one disjoint interval. Hall 20 rows overlap fully. Hall 30 has a single-day event.

Example 2

Input

HallEvents:
| hall_id | start_day  | end_day    |
|---------|------------|------------|
| 7       | 2025-06-01 | 2025-06-10 |
| 7       | 2025-06-03 | 2025-06-05 |
| 7       | 2025-06-10 | 2025-06-12 |
| 8       | 2025-06-01 | 2025-06-02 |
| 8       | 2025-06-04 | 2025-06-06 |

Output

[
  {"hall_id":7,"start_day":"2025-06-01","end_day":"2025-06-12"},
  {"hall_id":8,"start_day":"2025-06-01","end_day":"2025-06-02"},
  {"hall_id":8,"start_day":"2025-06-04","end_day":"2025-06-06"}
]

Explanation

For hall 7, nesting and boundary-touching rows belong to one merged island. Hall 8 contains two disjoint windows.

Example 3

Input

HallEvents:
[]

Output

[]

Explanation

No input rows means no consolidated windows.

Constraints

0 <= number of rows in HallEvents <= 50000
1 <= hall_id <= 1000000000
2000-01-01 <= start_day <= end_day <= 2035-12-31
Input rows may contain duplicates
Intervals are inclusive on both boundaries
Merge intervals only within the same hall_id
Return exactly columns: hall_id, start_day, end_day
Output row order is not graded
For SQL submissions, use SQLite 3.27.2 semantics
For Pandas submissions, return a DataFrame with columns: hall_id, start_day, end_day

HallEvents: | hall_id | start_day | end_day | |---------|------------|------------| | 10 | 2024-01-02 | 2024-01-04 | | 10 | 2024-01-04 | 2024-01-08 | | 10 | 2024-01-12 | 2024-01-14 | | 20 | 2024-02-05 | 2024-02-15 | | 20 | 2024-02-07 | 2024-02-09 | | 30 | 2024-03-01 | 2024-03-01 |

[ {"hall_id":10,"start_day":"2024-01-02","end_day":"2024-01-08"}, {"hall_id":10,"start_day":"2024-01-12","end_day":"2024-01-14"}, {"hall_id":20,"start_day":"2024-02-05","end_day":"2024-02-15"}, {"hall_id":30,"start_day":"2024-03-01","end_day":"2024-03-01"} ]

HallEvents: | hall_id | start_day | end_day | |---------|------------|------------| | 7 | 2025-06-01 | 2025-06-10 | | 7 | 2025-06-03 | 2025-06-05 | | 7 | 2025-06-10 | 2025-06-12 | | 8 | 2025-06-01 | 2025-06-02 | | 8 | 2025-06-04 | 2025-06-06 |