Katkat
Back

Compute unique duration by merging intervals

NuroData Engineer · Technical Screen
EasyData EngineerTechnical ScreenData Manipulation (SQL/Python)

Last updated

This question evaluates a data engineer's proficiency in temporal data manipulation and interval-set reasoning using SQL and Python, specifically skills in merging overlapping intervals and computing deduplicated cumulative durations.

Problem

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

Table: clips

columntypedescription
set_idINTSession/clip set identifier (e.g., 1–6)
run_idSTRINGDriving run identifier, formatted like YYYYMMDD_HHMMSS
start_timeINTClip start time offset (e.g., seconds)
end_timeINTClip end time offset (e.g., seconds)
set_numberINTDataset/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).