Given a table HallEvents that contains information about events held in different halls, write a SQL query to merge all overlapping events that occur in the same hall.
Two events are considered overlapping if they have at least one day in common. Events that overlap should be merged into a single event with the earliest start date and latest end date.
Return the result table showing the merged events for each hall in any order.
Table Schema
| Column Name | Type | Description |
|---|---|---|
hall_id
|
int | ID of the hall where the event is held |
start_day
|
date | Start date of the event |
end_day
|
date | End date of the event |
Input & Output
| hall_id | start_day | end_day |
|---|---|---|
| 1 | 2023-01-01 | 2023-01-03 |
| 1 | 2023-01-02 | 2023-01-04 |
| 1 | 2023-01-06 | 2023-01-07 |
| hall_id | start_day | end_day |
|---|---|---|
| 1 | 2023-01-01 | 2023-01-04 |
| 1 | 2023-01-06 | 2023-01-07 |
The first two events in hall 1 overlap (Jan 1-3 and Jan 2-4 share Jan 2-3), so they merge into one event from Jan 1-4. The third event (Jan 6-7) doesn't overlap with the merged event, so it remains separate.
| hall_id | start_day | end_day |
|---|---|---|
| 1 | 2023-01-01 | 2023-01-02 |
| 2 | 2023-01-01 | 2023-01-03 |
| 2 | 2023-01-02 | 2023-01-04 |
| hall_id | start_day | end_day |
|---|---|---|
| 1 | 2023-01-01 | 2023-01-02 |
| 2 | 2023-01-01 | 2023-01-04 |
Hall 1 has only one event, so no merging needed. Hall 2 has two overlapping events (Jan 1-3 and Jan 2-4) that merge into one event spanning Jan 1-4.
| hall_id | start_day | end_day |
|---|---|---|
| 1 | 2023-01-01 | 2023-01-02 |
| 1 | 2023-01-03 | 2023-01-04 |
| hall_id | start_day | end_day |
|---|---|---|
| 1 | 2023-01-01 | 2023-01-02 |
| 1 | 2023-01-03 | 2023-01-04 |
The events are adjacent (end of first event is Jan 2, start of second is Jan 3) but don't overlap since they don't share any common days. Both events remain separate.
Constraints
-
1 ≤ hall_id ≤ 1000 -
start_day ≤ end_day - Table may contain duplicate rows
- Dates are in YYYY-MM-DD format