Merge Overlapping Events in the Same Hall — LeetCode 2494 Python Solution

Table: HallEvents +-------------+------+ | Column Name | Type | +-------------+------+ | hall_id | int | | start_day | date | | end_day | date | +-------------+------+ This table may contain duplicates rows. Each row of this table indicates the start day and end day of an event and the hall in which the event is held.

+-------------+------+
| Column Name | Type |
+-------------+------+
| hall_id     | int  |
| start_day   | date |
| end_day     | date |
+-------------+------+
This table may contain duplicates rows.
Each row of this table indicates the start day and end day of an event and the hall in which the event is held.

import duckdb
import pandas as pd

def solution(hall_events: pd.DataFrame) -> pd.DataFrame:
    con = duckdb.connect()
    con.register("HallEvents", hall_events)
    return con.execute("""WITH
    S AS (
        SELECT
            hall_id,
            start_day,
            end_day,
            MAX(end_day) OVER (
                PARTITION BY hall_id
                ORDER BY start_day
            ) AS cur_max_end_day
        FROM HallEvents
    ),
    T AS (
        SELECT
            *,
            IF(
                start_day <= LAG(cur_max_end_day) OVER (
                    PARTITION BY hall_id
                    ORDER BY start_day
                ),
                0,
                1
            ) AS start
        FROM S
    ),
    P AS (
        SELECT
            *,
            SUM(start) OVER (
                PARTITION BY hall_id
                ORDER BY start_day
            ) AS gid
        FROM T
    )
SELECT hall_id, MIN(start_day) AS start_day, MAX(end_day) AS end_day
FROM P
GROUP BY hall_id, gid;""").df()