Overlapping Approved Leave Within a Team

Hard
243.

Overlapping Approved Leave Within a Team

HardJoinsDates

MySQL

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

ColumnType
emp_idint
full_namevarchar
teamvarchar

Primary key: emp_id.

Table: LeaveRequest

ColumnTypeValues
leave_idint
emp_idint
start_datedate
end_datedate
statusenumapproved, 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.

Example

The rows must come back in the order shown.

Employee

emp_idfull_nameteam
1Aarav SharmaPayments
2Diya IyerPayments
3Ishaan GuptaPayments
4Kavya NairSearch

LeaveRequest

leave_idemp_idstart_dateend_datestatus
1112025-05-052025-05-09approved
1222025-05-082025-05-12approved
1332025-05-092025-05-09approved
1442025-05-062025-05-08approved
1522025-05-012025-05-02pending
1612025-05-012025-05-02approved
1732025-05-122025-05-14rejected
1832025-05-132025-05-13approved

Output

teamemp_aemp_boverlap_startoverlap_endoverlap_days
Payments122025-05-082025-05-092
Payments132025-05-092025-05-091
Payments232025-05-092025-05-091
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
emp_idint
full_namevarchar
teamvarchar

Primary key: emp_id.

Table: LeaveRequest

ColumnType
leave_idint
emp_idint
start_datedate
end_datedate
statusenum(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_idfull_nameteam
1Aarav SharmaPayments
2Diya IyerPayments
3Ishaan GuptaPayments
4Kavya NairSearch

LeaveRequest

leave_idemp_idstart_dateend_datestatus
1112025-05-052025-05-09approved
1222025-05-082025-05-12approved
1332025-05-092025-05-09approved
1442025-05-062025-05-08approved
1522025-05-012025-05-02pending
1612025-05-012025-05-02approved
1732025-05-122025-05-14rejected
1832025-05-132025-05-13approved

Output

teamemp_aemp_boverlap_startoverlap_endoverlap_days
Payments122025-05-082025-05-092
Payments132025-05-092025-05-091
Payments232025-05-092025-05-091

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_end

Another 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 →