Compute unique duration by merging intervals

Problem

You have an autonomous-driving clip dataset where each row represents a time interval from a driving run.

Table: clips

column	type	description
set_id	INT	Session/clip set identifier (e.g., 1–6)
run_id	STRING	Driving run identifier, formatted like YYYYMMDD_HHMMSS
start_time	INT	Clip start time offset (e.g., seconds)
end_time	INT	Clip end time offset (e.g., seconds)
set_number	INT	Dataset/source identifier

Assume each interval is half-open: [start_time, end_time) so duration is end_time - start_time.

Intervals for the same run_id may:

• overlap (partially or fully),
• be duplicated/redundant,
• have gaps (non-contiguous).

Task

Compute, for each run_id, the cumulative deduplicated time: the total duration covered by the union of all intervals for that run_id (i.e., after merging overlaps).

Example input

For run 20250101_122334:

• set 1: [58, 70)
• set 2: [57, 69)
• set 3: [55, 72)
• set 4: [56, 71)
• set 5: [80, 100)

For run 20250102_101010:

• set 6: [43, 62)

Expected output

Return a result with:

• run_id
• cumulative_unique_time (same unit as the time offsets)

Using the example above, results would be:

• 20250101_122334 → 39
• 20250102_101010 → 19

Requirements

1. Write a SQL solution to compute cumulative_unique_time per run_id.
2. Write a Python solution to compute the same metric (given a list/dataframe of intervals).

Compute cumulative unique duration by merging overlapping intervals per run

Table drive_clip_intervals

column	type	constraints
set_id	INT	PRIMARY KEY, NOT NULL
run_id	VARCHAR(32)	NOT NULL
start_time	INT	NOT NULL
end_time	INT	NOT NULL
set_number	INT	NOT NULL

Sample data:

end_time	run_id	set_id	set_number	start_time
70	20250101_122334	1	1	58
69	20250101_122334	2	1	57
72	20250101_122334	3	1	55
71	20250101_122334	4	1	56
100	20250101_122334	5	1	80
62	20250102_101010	6	2	43

Expected output

每个 run_id 一行，返回合并区间后的累计去重时长 cumulative_unique_time（按 end_time - start_time 计算）。

cumulative_unique_time	run_id
37	20250101_122334
19	20250102_101010

Hints

1. 先按 run_id 分区、按 start_time 排序，判断当前区间是否与前面已覆盖到的最大 end_time 重叠。
2. 可以用窗口函数计算 running max(end_time)，并用“gaps and islands”思路给可合并的区间打组。