Date range overlap detection in SQL - Mini Project: Build & Apply

Date Range Overlap Detection

📖 Scenario: You are managing a booking system for a small hotel. Each booking has a start date and an end date. You want to find if any two bookings overlap in their date ranges to avoid double bookings.

🎯 Goal: Build a SQL query that detects overlapping date ranges between bookings in the database.

📋 What You'll Learn

Create a table called bookings with columns id, start_date, and end_date.

Insert sample booking data with specific date ranges.

Write a query to find pairs of bookings where the date ranges overlap.

Ensure the query excludes comparing a booking with itself.

💡 Why This Matters

🌍 Real World

Detecting overlapping date ranges is essential in booking systems, event scheduling, and resource allocation to avoid conflicts.

💼 Career

Database developers and analysts often write queries to find overlapping intervals to ensure data integrity and proper scheduling.

1

Create the bookings table and insert data

Create a table called bookings with columns id as integer primary key, start_date as date, and end_date as date. Then insert these exact rows: (1, '2024-06-01', '2024-06-05'), (2, '2024-06-04', '2024-06-10'), (3, '2024-06-11', '2024-06-15').

SQL

-- Create the bookings table and insert the specified rows
-- Your code here

Need a hint?

Use CREATE TABLE to define the table and INSERT INTO to add rows.

2

Add a helper condition for overlap detection

Create a variable or expression that will help detect if two date ranges overlap. Use the condition that two ranges overlap if the start date of one is less than or equal to the end date of the other and vice versa. Write this condition as part of a WHERE clause comparing b1 and b2 bookings.

SQL

CREATE TABLE bookings (
  id INTEGER PRIMARY KEY,
  start_date DATE,
  end_date DATE
);

INSERT INTO bookings (id, start_date, end_date) VALUES
  (1, '2024-06-01', '2024-06-05'),
  (2, '2024-06-04', '2024-06-10'),
  (3, '2024-06-11', '2024-06-15');

-- Add a WHERE clause condition to detect overlap between b1 and b2
-- Your code here

Need a hint?

Two date ranges overlap if one starts before the other ends and ends after the other starts.

3

Write the core query to find overlapping bookings

Write a SQL query that selects b1.id and b2.id from the bookings table aliased as b1 and b2. Use the overlap condition from Step 2 in the WHERE clause. Also, add a condition to exclude rows where b1.id = b2.id to avoid comparing a booking with itself.

SQL

CREATE TABLE bookings (
  id INTEGER PRIMARY KEY,
  start_date DATE,
  end_date DATE
);

INSERT INTO bookings (id, start_date, end_date) VALUES
  (1, '2024-06-01', '2024-06-05'),
  (2, '2024-06-04', '2024-06-10'),
  (3, '2024-06-11', '2024-06-15');

SELECT b1.id, b2.id
FROM bookings b1, bookings b2
WHERE b1.id  b2.id
  AND b1.start_date = b2.start_date
-- Your code here

Need a hint?

Use a self-join on the bookings table with aliases and apply the overlap condition and id inequality.

4

Complete the query with ordering and distinct pairs

Modify the query to select distinct pairs of overlapping bookings by adding DISTINCT before the select columns. Also, order the results by b1.id ascending and then b2.id ascending.

SQL

CREATE TABLE bookings (
  id INTEGER PRIMARY KEY,
  start_date DATE,
  end_date DATE
);

INSERT INTO bookings (id, start_date, end_date) VALUES
  (1, '2024-06-01', '2024-06-05'),
  (2, '2024-06-04', '2024-06-10'),
  (3, '2024-06-11', '2024-06-15');

-- Add DISTINCT and ORDER BY clauses to the query
-- Your code here

Need a hint?

Use DISTINCT to avoid duplicate pairs and ORDER BY to sort the results.