Date range overlap detection in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Detect overlapping date ranges between two bookings

Given a table bookings with columns id, start_date, and end_date, which SQL query correctly returns all pairs of bookings that overlap in their date ranges?

SQL

CREATE TABLE bookings (id INT, start_date DATE, end_date DATE);
INSERT INTO bookings VALUES
(1, '2024-01-01', '2024-01-10'),
(2, '2024-01-05', '2024-01-15'),
(3, '2024-01-20', '2024-01-25');

A SELECT a.id AS booking1, b.id AS booking2 FROM bookings a JOIN bookings b ON a.id <> b.id WHERE a.start_date > b.end_date OR a.end_date < b.start_date;

B SELECT a.id AS booking1, b.id AS booking2 FROM bookings a JOIN bookings b ON a.id = b.id WHERE a.start_date < b.end_date AND a.end_date > b.start_date;

C SELECT a.id AS booking1, b.id AS booking2 FROM bookings a JOIN bookings b ON a.id <> b.id WHERE a.start_date <= b.end_date AND a.end_date >= b.start_date;

D SELECT a.id AS booking1, b.id AS booking2 FROM bookings a JOIN bookings b ON a.id <> b.id WHERE a.start_date >= b.end_date AND a.end_date <= b.start_date;

💡 Hint

Think about when two date ranges overlap: they share at least one day in common.

Two date ranges overlap if the start of one is on or before the end of the other, and its end is on or after the other's start. Option C correctly uses this logic and excludes self-joins by checking a.id <> b.id.

📝 Syntax

intermediate

Identify the syntax error in date range overlap query

Which option contains a syntax error in the SQL query that tries to find overlapping date ranges between two tables events and reservations?

SQL

Tables:
events(event_id INT, start_date DATE, end_date DATE)
reservations(res_id INT, start_date DATE, end_date DATE)

A SELECT e.event_id, r.res_id FROM events e JOIN reservations r WHERE e.start_date <= r.end_date AND e.end_date >= r.start_date;

B SELECT e.event_id, r.res_id FROM events e JOIN reservations r ON e.start_date <= r.end_date AND e.end_date >= r.start_date WHERE e.event_id <> r.res_id;

C SELECT e.event_id, r.res_id FROM events e JOIN reservations r ON e.start_date <= r.end_date AND e.end_date >= r.start_date;

D SELECT e.event_id, r.res_id FROM events e JOIN reservations r ON e.start_date < r.end_date AND e.end_date > r.start_date;

💡 Hint

Check if the JOIN clause has a proper ON condition.

Option A is missing the ON keyword and condition after JOIN, which is required syntax for JOIN operations in SQL.

❓ optimization

advanced

Optimize date range overlap query for large datasets

You have a large table appointments with columns id, start_time, and end_time. Which query is the most efficient to find overlapping appointments without self-joining the entire table unnecessarily?

SQL

CREATE INDEX idx_start_time ON appointments(start_time);
CREATE INDEX idx_end_time ON appointments(end_time);

A SELECT a.id, b.id FROM appointments a JOIN appointments b ON a.id <> b.id AND a.start_time <= b.end_time AND a.end_time >= b.start_time;

B WITH filtered AS (SELECT * FROM appointments WHERE start_time < '2024-12-31') SELECT a.id, b.id FROM filtered a JOIN filtered b ON a.id <> b.id WHERE a.start_time <= b.end_time AND a.end_time >= b.start_time;

C SELECT a.id, b.id FROM appointments a JOIN appointments b ON a.id <> b.id WHERE a.start_time <= b.end_time AND a.end_time >= b.start_time;

D SELECT a.id, b.id FROM appointments a JOIN appointments b ON a.id <> b.id WHERE a.start_time < b.end_time AND a.end_time > b.start_time AND a.start_time < b.start_time;

💡 Hint

Try to push conditions into the JOIN clause to reduce the number of rows joined.

Option A places the overlap condition inside the JOIN clause, allowing the database engine to use indexes more effectively and reduce the join size compared to filtering after the join.

🔧 Debug

advanced

Why does this date range overlap query return duplicates?

The query below returns pairs of overlapping date ranges but includes duplicate pairs like (1,2) and (2,1). How can you fix it to avoid duplicates?

SQL

SELECT a.id AS id1, b.id AS id2 FROM bookings a JOIN bookings b ON a.start_date <= b.end_date AND a.end_date >= b.start_date WHERE a.id <> b.id;

A Use UNION ALL instead of JOIN.

B Add a condition to only select pairs where a.id < b.id in the WHERE clause.

C Remove the WHERE clause filtering a.id <> b.id.

D Change the JOIN to LEFT JOIN to avoid duplicates.

💡 Hint

Think about how to order pairs to avoid reversed duplicates.

Adding a.id < b.id ensures each pair is returned only once in a consistent order, preventing reversed duplicates like (2,1) when (1,2) is already included.

🧠 Conceptual

expert

Understanding date range overlap logic

Which of the following statements correctly describes the condition to detect if two date ranges [start1, end1] and [start2, end2] overlap?

A They overlap if start1 = start2 OR end1 = end2.

B They overlap if start1 > end2 OR end1 < start2.

C They overlap if start1 < start2 AND end1 > end2.

D They overlap if start1 <= end2 AND end1 >= start2.

💡 Hint

Overlap means the ranges share at least one common point in time.

Option D correctly states the condition for two ranges to overlap: the start of one is before or on the end of the other, and its end is after or on the start of the other.