Drill 12 of 12 in HR questions
0

Interview Question Bank · Self-join · range overlap · de-duplicating pairs

12. Overlapping leave on the same team

HardHRQuestion #192

Concept: Self-join · range overlap · de-duplicating pairsshow

Two date ranges overlap when each starts on or before the other ends: a.start <= b.end AND b.start <= a.end. With inclusive end dates, ranges that touch share that day, so the test needs <=. Self-join the table to compare every pair, and use a.id < b.id so each pair appears once (never twice, never with itself). The shared stretch runs from GREATEST of the starts to LEAST of the ends.

Example (different data):

-- ends_at is exclusive here, so touching bookings don't clash
SELECT a.room, a.id AS booking_1, b.id AS booking_2,
  GREATEST(a.starts_at, b.starts_at) AS clash_from
FROM room_bookings a
JOIN room_bookings b ON b.room = a.room AND a.id < b.id
  AND a.starts_at < b.ends_at AND b.starts_at < a.ends_at;

Your task

Quillmoor's team leads want to see where approved time off overlaps within a team so they can plan cover. Leave dates are inclusive: end_date is the last day off.

For every pair of approved leave requests from two different employees on the same team that share at least one day, return team, employee_1 (the employee on the request with the lower id), employee_2, overlap_start, overlap_end and overlap_days (days in the shared stretch, counting both ends). Sort by overlap_start, then team, then employee_1.

Starting Postgres in your browser…

Checking your access…