SQL Overlapping Date Ranges: Interview Problems With Boundaries and Open Ends

Two reservations ending and starting on the same date may overlap, or they may be perfectly compatible. The SQL operator cannot decide that for you. First establish whether the end is included, what a missing end means, and whether you want conflicting pairs or the bookings involved in any conflict.

For nonempty half-open intervals, the core rule is simple: each interval must start before the other ends. The hard part is preserving that rule when the data contains open ends, invalid rows, multiple rooms, and nested intervals. Find gaps between intervals is useful adjacent practice for explaining those boundary choices.

Evidence boundary: database semantics below are linked to official PostgreSQL documentation. The booking exercise, expected results, and interview recommendations are original. The portable SQL was executed in DuckDB 1.5.2; the PostgreSQL range example was checked against documentation, not executed on a local PostgreSQL server. No candidate report is used to predict an employer's questions.

Define the interval contract before writing the join

Use a clear convention for this exercise: a booking occupies dates in [start_date, end_date). The start is included; the end is excluded. Both dates are calendar dates in the same business context, and start_date is required.

Under that contract, [Jan 1, Jan 5) and [Jan 5, Jan 8) do not overlap. The first no longer occupies January 5. This convention fits a checkout date that becomes available to another booking, but it is an assumption to confirm, not a universal rule for all date data.

Official database semantics: PostgreSQL range types distinguish inclusive and exclusive bounds and support unbounded ranges. That vocabulary is useful even when your schema stores two ordinary columns rather than a range value.

For this exercise, a NULL end explicitly means “continues indefinitely.” It does not mean an unknown date that might be before or after another booking. If the source uses NULL for unknown information, you cannot silently reinterpret it as infinity; the result may need a separate indeterminate category.

Reject finite intervals where the start is after the end. An equal start and end is empty under this half-open convention, so exclude it from conflict detection. Do not use the same exclusion if the business treats a zero-duration event as a point that can conflict.

Derive overlap from the ways intervals can be disjoint

Consider two valid finite intervals, A and B. They are disjoint if A ends at or before B starts, or B ends at or before A starts:

A.end <= B.start OR B.end <= A.start

Negating that condition gives:

A.start < B.end AND B.start < A.end

The two comparisons handle partial overlaps, containment, and identical nonempty intervals. Checking only whether A's start lies inside B misses cases where A surrounds B. Checking only one inequality can include intervals that are entirely before or after each other.

If both ends are inclusive, valid intervals use non-strict overlap comparisons instead. Do not switch operators without changing the stated model and tests. “Touching” is a business boundary decision, not a small implementation detail.

Official SQL behavior: PostgreSQL's comparison documentation explains inclusive BETWEEN behavior and ordinary comparisons involving NULL. Those semantics are why a casual BETWEEN rewrite or an unhandled NULL end can change the answer.

In an interview, narrate the derivation before writing a long predicate. It gives you a way to recover if you forget the formula and makes your assumptions visible to the interviewer.

Work through a fixture that breaks naive answers

Assume unique booking IDs and the following rows. All dates are in January 2026. Room R and room S are different resources.

Booking	Room	Start	End	Why it is included
1	R	Jan 1	Jan 5	Base interval
2	R	Jan 5	Jan 8	Touches booking 1
3	R	Jan 2	Jan 3	Nested inside booking 1
4	R	Jan 4	NULL	Open-ended booking
5	R	Jan 7	Jan 9	Overlaps several bookings
6	S	Jan 2	Jan 10	Same dates, different room
7	R	Jan 3	Jan 3	Empty interval
8	R	Jan 9	Jan 8	Reversed interval

The requested output is one row per unordered conflicting pair in the same room. After excluding bookings 7 and 8, the answer is:

booking_a | booking_b
1         | 3
1         | 4
2         | 4
2         | 5
4         | 5

Booking 1 does not conflict with 2 because they only touch. Booking 3 does not conflict with 4 because it ends before 4 starts. Booking 6 has no partner in its room, regardless of how its dates intersect room R's dates.

Write down this output before running the query. Otherwise, a plausible-looking result can conceal an extra pair, a missing open-ended match, or a duplicate in the opposite order.

Write a self-join with explicit open-end handling

The following query assumes start_date and room_id are non-NULL and booking_id is a unique identifier. It uses a CTE to make the valid input set explicit:

WITH valid AS (
  SELECT *
  FROM bookings
  WHERE end_date IS NULL OR start_date < end_date
)
SELECT
  a.booking_id AS booking_a,
  b.booking_id AS booking_b
FROM valid a
JOIN valid b
  ON a.room_id = b.room_id
 AND a.booking_id < b.booking_id
 AND (b.end_date IS NULL OR a.start_date < b.end_date)
 AND (a.end_date IS NULL OR b.start_date < a.end_date)
ORDER BY booking_a, booking_b;

The room condition restricts conflicts to the same resource. The ID inequality removes self-pairs and mirrored duplicates. It does not require IDs to be chronological; it only chooses one orientation for each distinct pair.

Each NULL branch represents the agreed unbounded end. If B has no end, A starts before B's end by definition of this model; the other comparison still determines whether the intervals meet. If both ends are open, two valid bookings in the same room overlap.

Do not replace NULL with an arbitrary distant date unless the data contract guarantees that date is beyond every relevant value. A sentinel such as December 31, 9999 can create a hidden upper limit and may interact differently with database date ranges.

The validity CTE is useful for an exercise, but silently dropping bad production rows can hide a data-quality problem. Report rejected rows separately or enforce an appropriate input rule. For this fixture, that report must identify IDs 7 and 8.

Check pair counts and affected bookings separately

The query returns five conflict pairs. It also happens to involve five distinct bookings: 1, 2, 3, 4, and 5. Those counts are equal here by coincidence, not by definition.

To count affected bookings from a pairs result, combine both sides with UNION, which removes repeated IDs:

SELECT COUNT(*) AS affected_bookings
FROM (
  SELECT booking_a AS booking_id FROM pairs
  UNION
  SELECT booking_b AS booking_id FROM pairs
) affected;

Here pairs means the preceding conflict query materialized as a CTE or view. Counting just booking_a misses bookings that appear only on the right. Using UNION ALL counts appearances in pairs rather than unique affected bookings.

If the question asks whether each booking has any conflict, an EXISTS query is another suitable shape. For that version, use a.booking_id <> b.booking_id to exclude only the same row; the unordered-pair orientation is unnecessary because you are producing one Boolean result per booking.

Clarify the output grain before optimizing. A query that is correct for pair enumeration can be the wrong answer for distinct rooms, affected customers, or total occupied days.

Why checking only the previous end can fail

A tempting solution sorts intervals by start and compares each start with LAG(end_date). That examines the immediately previous interval, not every earlier interval that may still be active.

Consider [Jan 1, Jan 10), [Jan 2, Jan 3), and [Jan 4, Jan 5). The third interval overlaps the first. Its immediate predecessor ends on January 3, so a simple previous-end check misses the conflict.

For the narrower task “does this interval overlap any earlier interval?”, a running maximum of earlier finite ends can help. Open-ended intervals require an additional representation or flag, and ties need a deterministic order. That approach still does not enumerate every conflicting pair.

State the distinction when an interviewer suggests a window function. A window summary may answer an existence question efficiently while discarding information needed for a complete pair list. Do not promise that LAG and a self-join are interchangeable.

Also distinguish overlap detection from merging intervals into islands. Merging produces a union of covered periods; it does not preserve which original bookings conflict. Solve the requested problem rather than substituting a neighboring interval pattern.

Use PostgreSQL range operators when the dialect permits them

Official PostgreSQL feature: the range operator reference defines && as overlap. With the same validated input and explicit NULL-as-unbounded contract, the range-specific predicate can be written as:

-- PostgreSQL-specific; use alongside room and ID conditions.
daterange(a.start_date, a.end_date, '[)')
  && daterange(b.start_date, b.end_date, '[)')

The explicit bounds keep the intended convention visible. This is a dialect-specific alternative, not a portable replacement for every SQL engine. The example was documentation-reviewed; the portable self-join above was the query executed for this article.

Do not confuse a detection query with a concurrency guarantee. Two transactions can both check for a conflict before either writes. Official constraint documentation: PostgreSQL constraints includes exclusion constraints for relationships between rows. A production booking design should choose a database-supported enforcement strategy appropriate to its schema and isolation requirements.

Input constraints also need careful NULL semantics. A check that evaluates to unknown is not the same as a check that rejects a row. Combine required-column rules with an explicit finite-end validity condition rather than expecting one loose comparison to define the entire contract.

Explain performance without promising an impossible output size

A self-join can produce many pairs. If every booking in one room overlaps every other booking, the answer itself has n * (n - 1) / 2 rows. No implementation can emit that complete output in less than proportional output work.

Filter by the relevant resource and requested time horizon when the problem allows it. Inspect the actual execution plan and data distribution before claiming an index will solve the problem. An index useful for one endpoint condition may not eliminate all work for the other.

PostgreSQL range indexes can support range operations, but the schema, operator, selectivity, and query plan still matter. Explain what you would measure: candidate rows examined, total pairs emitted, memory use, and whether one heavily booked room dominates the workload.

If only a conflict flag is required, avoid generating a full pair list merely to collapse it afterward. If the task asks for overlap duration, first intersect the intervals and define how an unbounded result should be reported or clipped to a finite analysis window.

Use boundary tests to challenge your own query

The exercise was checked against the five expected pairs in DuckDB. Four deliberately incorrect variants changed the result: inclusive comparisons added the touching pair, removing NULL handling lost open-ended matches, removing the room condition introduced cross-room matches, and using unequal IDs emitted mirrored pairs.

An independent finite-interval check compared the two-inequality rule with set intersection across 225 pairs of nonempty integer intervals. That supports the core half-open predicate; it does not test a PostgreSQL execution plan or establish production concurrency safety.

Question	Transferable reasoning
Find gaps between intervals	Define adjacency and endpoints
Debug row loss after SQL joins	Check which predicates remove rows
Solve window-function SQL without joins	Explain what a window summary can preserve
Write SQL for repeat churn	Keep event and period definitions explicit
Query conversion and retention with SQL windows	Separate time windows from output grain

Start with Find gaps between intervals, then change one endpoint convention and state which expected results must change. Being able to explain that change is stronger evidence of understanding than memorizing an overlap formula.

Sources and further reading

• PostgreSQL: Range types
• PostgreSQL: Comparison functions and operators
• PostgreSQL: Range operators
• PostgreSQL: Constraints