SQL Interval Subtraction Interview Exercise: Remove Maintenance Windows from Availability

SQL interval subtraction asks for the surviving pieces of availability after maintenance is removed. Normalize each resource's availability into a union, normalize its maintenance into another union, then subtract the two sets. An overlap join can identify intersecting rows, but it cannot by itself return every remaining fragment without duplicates or lost boundaries.

This original interview exercise uses half-open integer-minute intervals and PostgreSQL multiranges. PracHub's overlapping-date-range practice develops boundary reasoning; this case advances from detecting intersections to constructing the exact surviving availability set.

PostgreSQL documentation defines ranges, multiranges and their operators. The resource data, fragment ledger and independent oracle are original practice. PostgreSQL 18.6 executed the query locally against synthetic data. No production calendar, timestamp-zone workload or concurrent reservation service was tested.

Fix the interval contract before writing joins

Represent each interval as [start, end): start included, end excluded. Integer values count minute boundaries in this lab, and every input must satisfy start < end. A window from zero to five contains five unit-minute cells; a maintenance window beginning at five does not remove any part of it.

This convention prevents a shared endpoint from being counted twice when adjacent intervals meet. It also makes duration end - start. Do not switch halfway through the solution to closed intervals merely because a neighboring coding problem uses them. With closed integer ranges, endpoint normalization and duration arithmetic follow a different representation.

The resource key must remain part of every set operation. Maintenance on resource r1 must not remove availability on r2 even when their clock coordinates overlap. A missing join condition can produce a syntactically valid query with a convincing-looking fragment list that is wrong for every resource sharing the same time range.

Empty or reversed input intervals are rejected by the exercise's table constraints. PostgreSQL itself can represent empty ranges, but our application contract chooses not to accept zero-length scheduling records. Keep that policy separate from the database type's full capabilities. An invalid row should not be silently transformed into a valid but unintended schedule.

Normalize the original resource ledger

Resource r1 starts with availability [0,20), [18,30) and [40,50). The first two overlap and form [0,30); the last remains separate. The union contains forty available minutes, not forty-two. Summing the original row durations before normalization double-counts the overlap from eighteen to twenty.

Its maintenance rows are [-5,2), [5,10), [8,12), [12,15), [25,45) and [48,60). Overlapping and touching maintenance produces the union [-5,2), [5,15), [25,45) and [48,60). Maintenance extending outside availability is legitimate input; only its intersection with available time can remove time.

The intervals from five through fifteen are particularly useful. Treating them as independent cuts against every original availability row can create repeated fragments and subtract the same minutes more than once. Once they are unioned, the exclusion is a single continuous span with a clear effect.

The expected r1 survivors are [2,5), [15,25) and [45,48). Their durations are three, ten and three minutes, totaling sixteen. The removal from the availability union is therefore twenty-four minutes. That is less than the full maintenance union because some maintenance lies outside the resource's available schedule.

These three resources distinguish partial removal, full removal and endpoint-only contact. An implementation that returns one row per original availability record cannot naturally express all three outcomes without additional normalization and fragment logic.

Use multiranges because subtraction may split a range

A range represents one contiguous span. Subtracting an interior maintenance interval can produce two disconnected spans, so a single-range result is insufficient. PostgreSQL's range-operator documentation describes this limitation and the multirange operations that can represent disconnected results.

For example, [0,10) minus [3,7) must produce [0,3) and [7,10). The local lab deliberately executes a single-range subtraction and verifies that PostgreSQL rejects the noncontiguous result. Catching that failure is more informative than assuming the operator will quietly manufacture a pair of rows.

A multirange holds the ordered, nonoverlapping component ranges of a set. The range-types documentation explains this representation. It maps naturally to the desired schedule: a resource can have no available time, one continuous window or several disconnected windows after maintenance.

The aggregate range_agg builds a union from input ranges. That is exactly the normalization required before subtraction, rather than a convenient formatting trick. If two rows overlap, the multirange union represents their shared time once. If adjacent discrete half-open spans form one continuous set, the normalized representation can merge them.

Write the union-minus-union query

The query assumes two tables, availability(resource,s,e) and maintenance(resource,s,e), with integer bounds and the input checks already described. It first creates one multirange per resource on each side. A left join preserves resources that have availability but no maintenance.

WITH a AS (
  SELECT resource, range_agg(int4range(s,e,'[)')) AS spans
  FROM availability
  GROUP BY resource
), m AS (
  SELECT resource, range_agg(int4range(s,e,'[)')) AS spans
  FROM maintenance
  GROUP BY resource
)
SELECT a.resource,
       lower(piece) AS start_minute,
       upper(piece) AS end_minute
FROM a
LEFT JOIN m USING (resource)
CROSS JOIN LATERAL unnest(
  a.spans - COALESCE(m.spans, '{}'::int4multirange)
) AS piece
ORDER BY a.resource, start_minute;

The explicitly typed empty multirange means “remove nothing” when maintenance is absent. It is not an arbitrary NULL replacement. Without that handling, SQL null propagation could turn an otherwise valid availability set into an unknown subtraction result rather than leaving it intact.

unnest converts each surviving component range into an output row. For fully removed r2, there is nothing to unnest and therefore no fragment row. If the report also needs a resource-level zero-availability row, build that summary separately or join against the resource population. Do not return a fabricated zero-length fragment as a substitute.

The query's output is intentionally a fragment relation, not a Boolean overlap flag or a duration total. Those summaries can be derived from the fragments afterward. Returning the pieces first preserves evidence about exactly which time remains, making incorrect boundary behavior much easier to diagnose.

Validate coverage with a different representation

The independent coverage oracle enumerates integer-minute unit cells. It enumerates every cell covered by any availability interval, every cell covered by any maintenance interval and the set difference between them. It then expands the SQL output fragments into cells and compares the sets for exact equality.

This works because the lab's bounds are integers and its unit is one minute. It does not approximate continuous timestamp coverage by rounding. For this declared discrete domain, matching the cell sets proves that the returned fragments cover exactly the expected minutes in each generated case.

The lab checks 300 random resource cases with overlapping inputs, variable lengths and exclusions extending outside availability. It also verifies that result fragments are ordered, nonoverlapping, maximally separated and have a summed duration equal to the number of surviving cells. These invariants catch extra fragments, missing time and duplicate coverage.

Checking only total duration would miss some wrong fragments. Returning the wrong three minutes can preserve the same total duration. A fragment-count assertion is also insufficient because different sets can have the same number of components. Exact coverage comparison supplies a different representation of the requirement instead of mirroring the multirange implementation.

The deterministic ledger and two rejection probes remain separate from the randomized checks. A zero-length input fails the table contract; a disconnected single-range difference fails the type's result constraint. Those failures test different boundaries and should not be collapsed into one generic “invalid input” claim.

Explain the tempting shortcuts that lose information

An anti-join that discards every availability row touching maintenance is too aggressive. Availability [0,30) overlaps maintenance [5,15), but it still contains [0,5) and [15,30). Discarding the entire row answers “which original windows were untouched,” not “which time remains available.”

Subtracting total maintenance duration is also wrong when maintenance overlaps itself or extends beyond availability. In r1, nested maintenance and outside portions would distort the result. The correct duration is the measure of the availability union minus the maintenance union, not the sum of input lengths with one total subtracted from another.

A pairwise overlap join can be a useful intermediate diagnostic, but it multiplies rows when one availability range intersects several maintenance ranges. Producing a fragment from each pair independently can return overlapping pieces or miss later cuts. Normalization and a set-based subtraction make the intended operation explicit.

If multiranges are unavailable in the chosen SQL engine, an alternative is a boundary-event sweep or an iterative fragment algorithm. Explain that algorithm's invariant and validate against an independent oracle. Do not call a PostgreSQL-specific operator portable SQL, and do not assume a different database's range subtraction has identical output or type semantics.

Translate cautiously to real calendar time

Replacing integer minutes with timestamps changes the data contract. Choose whether bounds describe absolute instants or local wall-clock times, select a timestamp range type and define how time zones and daylight-saving transitions are handled. The integer lab does not verify those choices by analogy.

Unbounded ranges require an explicit duration policy because an infinite surviving interval has no finite minute total. NULL bounds may represent unbounded endpoints in range constructors, which differs from a missing or invalid scheduling record. If the application forbids them, enforce that requirement before calculating a finite availability metric.

Large schedules also need workload-specific performance evidence. Indexes, grouping cardinality and range complexity can affect execution, but our three-resource fixture and 300 small cases are correctness checks rather than a benchmark. Inspect an actual plan and representative data before promising that the query scales to a particular scheduling volume.

Finally, this is a read-only derivation over input schedules. It does not enforce reservation concurrency, prevent conflicting writes or authorize maintenance changes. PostgreSQL exclusion constraints address a related integrity problem, but they do not replace the set-difference query that returns remaining availability.

For an availability report, distinguish resources with no input schedule from resources whose schedule was completely removed. Both can produce no fragment rows, but their operational meanings differ. A resource population table can preserve that distinction in a summary with status and total duration. Do not infer it from the fragment relation alone. In our deterministic ledger, r2 is explicitly scheduled and fully removed, whereas an absent resource has no declared availability to subtract from. The lab's output demonstrates the first case without claiming that missing schedules are automatically equivalent to zero capacity.

Practice each prerequisite without confusing the final task

The linked interval records develop union, intersection, gaps and overrides. They are relevant prerequisites, not five identical subtraction prompts or predictions of an employer's questions. Their endpoint conventions may differ; restate our half-open contract when adapting any of them.

Use the gap exercise to explain r1's surviving windows, then add r2's full removal and r3's touching maintenance. A complete answer returns exact fragments, accounts for all sixteen surviving r1 minutes and states which calendar or database behaviors remain outside the executed test.

Sources and Further Reading

• PostgreSQL: Range and multirange types
• PostgreSQL: Range and multirange functions and operators
• PostgreSQL: Aggregate functions, including range_agg

Sources checked November 3, 2026. PostgreSQL 18.6 ran in a private local Unix-socket lab and was stopped after verification; no production database was changed.