Overlapping Approved Leave Within a Team — SQL Joins Problem
Team leads do not want two people from the same team away at the same time.
- Difficulty: Hard
- Topics: Joins, Dates
- Dialect: MySQL
- Problem: #243
Problem statement
Team leads do not want two people from the same team away at the same time. Leave dates are inclusive: a leave from 2025-05-05 to 2025-05-07 covers three days, and two leaves overlap when they share at least one day. Only approved requests matter.
For every pair of approved leave requests of two different employees in the same team that overlap, return team, emp_a and emp_b (the two emp_ids, smaller first), overlap_start, overlap_end and overlap_days. Order by overlap_start, then emp_a, then emp_b, then overlap_end.
Tables
Table: Employee
| Column | Type |
|---|---|
| emp_id | int |
| full_name | varchar |
| team | varchar |
Primary key: emp_id.
Table: LeaveRequest
| Column | Type |
|---|---|
| leave_id | int |
| emp_id | int |
| start_date | date |
| end_date | date |
| status | enum(approved, pending, rejected) |
Primary key: leave_id.
start_date and end_date are both days of leave (end_date ≥ start_date). One employee's own approved leaves never overlap each other.
Examples
Example 1
Employee
| emp_id | full_name | team |
|---|---|---|
| 1 | Aarav Sharma | Payments |
| 2 | Diya Iyer | Payments |
| 3 | Ishaan Gupta | Payments |
| 4 | Kavya Nair | Search |
LeaveRequest
| leave_id | emp_id | start_date | end_date | status |
|---|---|---|---|---|
| 11 | 1 | 2025-05-05 | 2025-05-09 | approved |
| 12 | 2 | 2025-05-08 | 2025-05-12 | approved |
| 13 | 3 | 2025-05-09 | 2025-05-09 | approved |
| 14 | 4 | 2025-05-06 | 2025-05-08 | approved |
| 15 | 2 | 2025-05-01 | 2025-05-02 | pending |
| 16 | 1 | 2025-05-01 | 2025-05-02 | approved |
| 17 | 3 | 2025-05-12 | 2025-05-14 | rejected |
| 18 | 3 | 2025-05-13 | 2025-05-13 | approved |
Output
| team | emp_a | emp_b | overlap_start | overlap_end | overlap_days |
|---|---|---|---|---|---|
| Payments | 1 | 2 | 2025-05-08 | 2025-05-09 | 2 |
| Payments | 1 | 3 | 2025-05-09 | 2025-05-09 | 1 |
| Payments | 2 | 3 | 2025-05-09 | 2025-05-09 | 1 |
How to solve Overlapping Approved Leave Within a Team
Pairing rows of one table means a self join. Join LeaveRequest a to LeaveRequest b with a.emp_id < b.emp_id: that excludes pairing an employee with themselves and lists every pair once, with the smaller id as emp_a. Join Employee on both sides and require the same team; keep only approved requests on both sides.
The overlap test for inclusive ranges is the classic a.start <= b.end AND b.start <= a.end: two ranges fail to overlap only when one ends before the other starts. The equality cases matter — a leave ending on the 9th and a one-day leave on the 9th share that day. The shared stretch runs from GREATEST(a.start, b.start) to LEAST(a.end, b.end), and with inclusive dates its length is DATEDIFF(end, start) + 1.
The alternative computes the would-be overlap for every same-team pair first (with CASE instead of GREATEST/LEAST) and keeps the pairs where it is non-empty, overlap_start <= overlap_end — an equivalent way to say the ranges intersect. The self join is quadratic in the leaves per team; an index on (status, start_date) lets an engine prune pairs by date.
Reference solution (MySQL)
SELECT ea.team, a.emp_id AS emp_a, b.emp_id AS emp_b,
GREATEST(a.start_date, b.start_date) AS overlap_start,
LEAST(a.end_date, b.end_date) AS overlap_end,
DATEDIFF(LEAST(a.end_date, b.end_date), GREATEST(a.start_date, b.start_date)) + 1 AS overlap_days
FROM LeaveRequest a
JOIN LeaveRequest b ON a.emp_id < b.emp_id AND a.start_date <= b.end_date AND b.start_date <= a.end_date
JOIN Employee ea ON ea.emp_id = a.emp_id
JOIN Employee eb ON eb.emp_id = b.emp_id AND eb.team = ea.team
WHERE a.status = 'approved' AND b.status = 'approved'
ORDER BY overlap_start, emp_a, emp_b, overlap_endAnother way
WITH approved AS (
SELECT l.emp_id, l.start_date, l.end_date, e.team
FROM LeaveRequest l JOIN Employee e ON e.emp_id = l.emp_id
WHERE l.status = 'approved'
)
SELECT team, emp_a, emp_b, overlap_start, overlap_end, DATEDIFF(overlap_end, overlap_start) + 1 AS overlap_days
FROM (
SELECT a.team, a.emp_id AS emp_a, b.emp_id AS emp_b,
CASE WHEN a.start_date > b.start_date THEN a.start_date ELSE b.start_date END AS overlap_start,
CASE WHEN a.end_date < b.end_date THEN a.end_date ELSE b.end_date END AS overlap_end
FROM approved a JOIN approved b ON a.team = b.team AND a.emp_id < b.emp_id
) p
WHERE overlap_start <= overlap_end
ORDER BY overlap_start, emp_a, emp_b, overlap_end← Total Team Size and Payroll Under Each Manager · Unplanned Absence Streaks of Three Working Days →