return it. Note that we only consider the events of each hall separately.

Solution

Method 1 – Window Functions and Grouping

Intuition

To merge overlapping events in the same hall, sort events by hall and start day, then use window functions to group overlapping intervals. For each hall, assign a group to each event where the event overlaps with the previous one, and then aggregate the minimum start and maximum end for each group.

Approach

Sort events by hall_id and start_day.
For each hall, use a window function to assign a group number that increments when an event does not overlap with the previous event.
For each group, aggregate the minimum start_day and maximum end_day.
Return the merged intervals for each hall.

Code

[{"language":"MySQL","code":"WITH ordered AS (\n  SELECT hall_id, start_day, end_day,\n    ROW_NUMBER() OVER (PARTITION BY hall_id ORDER BY start_day) AS rn\n  FROM HallEvents\n),\ngroups AS (\n  SELECT hall_id, start_day, end_day, rn,\n    SUM(CASE WHEN start_day > LAG(end_day) OVER (PARTITION BY hall_id ORDER BY start_day) THEN 1 ELSE 0 END)\n      OVER (PARTITION BY hall_id ORDER BY start_day) AS grp\n  FROM ordered\n)\nSELECT hall_id, MIN(start_day) AS start_day, MAX(end_day) AS end_day\nFROM groups\nGROUP BY hall_id, grp;","lang":"sql","html":"<pre class=\"shiki shiki-themes github-light github-dark\" style=\"background-color:#fff;--shiki-dark-bg:#24292e;color:#24292e;--shiki-dark:#e1e4e8\" tabindex=\"0\"><code><span class=\"line\"><span style=\"color:#D73A49;--shiki-dark:#F97583\">WITH</span><span style=\"color:#24292E;--shiki-dark:#E1E4E8\"> ordered </span><span style=\"color:#D73A49;--shiki-dark:#F97583\">AS</span><span style=\"color:#24292E;--shiki-dark:#E1E4E8\"> (</span></span>\n<span class=\"line\"><span style=\"color:#D73A49;--shiki-dark:#F97583\">  SELECT</span><span style=\"color:#24292E;--shiki-dark:#E1E4E8\"> hall_id, start_day, end_day,</span></span>\n<span class=\"line\"><span style=\"color:#005CC5;--shik

Related Tags