Measure incident coverage without double counting
Description
Report the total number of minutes covered by at least one incident for each team. Tables: teams(team_id TEXT PRIMARY KEY) and incidents(id INTEGER PRIMARY KEY, team_id TEXT, start_min INTEGER, end_min INTEGER). Every incident references an existing team. IDs are unique. Each incident is a half-open interval [start_min, end_min), with integer bounds 0 <= start_min < end_min <= 1000. Team IDs are lowercase ASCII letters. Overlapping, duplicate, and nested incidents count each minute only once. Adjacent intervals have no gap. Compute each team's union independently; an incident for one team cannot fill a gap for another. Include teams without incidents with covered_minutes = 0. Input order and incident ID order do not determine interval order. Output columns: team_id, covered_minutes. Sort team_id ascending. An empty teams table produces no rows.
Examples
CREATE TABLE teams(team_id TEXT PRIMARY KEY); CREATE TABLE incidents(id INTEGER PRIMARY KEY, team_id TEXT NOT NULL, start_min INTEGER NOT NULL, end_min INTEGER NOT NULL); INSERT INTO teams VALUES ('a'); INSERT INTO incidents VALUES (1,'a',2,8);a|6The half-open interval [2,8) covers six minutes.
CREATE TABLE teams(team_id TEXT PRIMARY KEY); CREATE TABLE incidents(id INTEGER PRIMARY KEY, team_id TEXT NOT NULL, start_min INTEGER NOT NULL, end_min INTEGER NOT NULL); INSERT INTO teams VALUES ('a'); INSERT INTO incidents VALUES (1,'a',0,20),(2,'a',2,3),(3,'a',10,25);a|25The nested [2,3) incident adds no coverage. [10,25) overlaps [0,20), giving one union [0,25) of 25 minutes.
Constraints
- •
SQLite 3.27 compatible SELECT query; window functions and CTEs are available. - •
Return columns in the specified order; separate columns with the engine output format. - •
At most 500 incidents and 100 teams. Team IDs use lowercase ASCII letters.
Ready to solve this problem?
Practice solo and sharpen your skills for technical interviews.