Double-Logged Rider Shifts That Overlap — SQL Joins Problem
A rider who logs in on two phones at once can earn login-hour incentives twice. Audit finds every pair of shifts of the same rider whose time ranges overlap.
- Difficulty: Hard
- Topics: Joins, Dates
- Dialect: MySQL
- Problem: #168
Problem statement
A rider who logs in on two phones at once can earn login-hour incentives twice. Audit finds every pair of shifts of the same rider whose time ranges overlap. A shift runs from login_at to logout_at; two shifts that only touch (one ends at the very instant the other starts) do not overlap. A shift lying entirely inside another does.
Return each overlapping pair once with columns rider_id, first_shift (the smaller shift_id), second_shift (the larger) and overlap_minutes (whole minutes during which both shifts were open). Order the rows by rider_id, then first_shift, then second_shift.
Tables
Table: RiderShift
| Column | Type |
|---|---|
| shift_id | int |
| rider_id | int |
| login_at | datetime |
| logout_at | datetime |
Primary key: shift_id.
One row per closed login session; logout_at is always later than login_at.
Examples
Example 1
RiderShift
| shift_id | rider_id | login_at | logout_at |
|---|---|---|---|
| 1 | 21 | 2024-08-05 08:00:00 | 2024-08-05 12:00:00 |
| 2 | 21 | 2024-08-05 11:30:00 | 2024-08-05 15:00:00 |
| 3 | 21 | 2024-08-05 15:00:00 | 2024-08-05 18:00:00 |
| 4 | 22 | 2024-08-05 09:00:00 | 2024-08-05 17:00:00 |
| 5 | 22 | 2024-08-05 10:00:00 | 2024-08-05 11:15:00 |
| 6 | 22 | 2024-08-05 16:30:00 | 2024-08-05 20:00:00 |
| 7 | 23 | 2024-08-05 10:00:00 | 2024-08-05 14:00:00 |
| 8 | 21 | 2024-08-05 07:00:00 | 2024-08-05 08:00:00 |
Output
| rider_id | first_shift | second_shift | overlap_minutes |
|---|---|---|---|
| 21 | 1 | 2 | 30 |
| 22 | 4 | 5 | 75 |
| 22 | 4 | 6 | 30 |
How to solve Double-Logged Rider Shifts That Overlap
Overlap is a relation between two rows of the same table, so the query is a self join of RiderShift on rider_id. Requiring a.shift_id < b.shift_id does two jobs: a shift is never paired with itself, and each unordered pair appears once, with the smaller id first as the output asks.
The overlap test is the classic one for intervals: [s1, e1) and [s2, e2) overlap when s1 < e2 AND s2 < e1. It covers every shape — partial overlap from either side, and one shift inside another (shift 5 inside shift 4). The strict < is what makes touching shifts (shift 2 ends at 15:00, shift 3 starts at 15:00) not an overlap. Its negation, "one ends before or when the other starts", is the alternative's NOT (e1 <= s2 OR e2 <= s1).
The shared stretch runs from the later of the two logins to the earlier of the two logouts, GREATEST(login) to LEAST(logout), and TIMESTAMPDIFF gives its minutes; a CASE picks the same two instants. The self join compares every pair of a rider's shifts — quadratic per rider, which is fine for a day's shifts; sorting by login and comparing neighbours with LAG would find overlaps with the previous shift only, missing a long shift that covers several later ones.
Reference solution (MySQL)
SELECT a.rider_id, a.shift_id AS first_shift, b.shift_id AS second_shift,
TIMESTAMPDIFF(MINUTE, GREATEST(a.login_at, b.login_at), LEAST(a.logout_at, b.logout_at)) AS overlap_minutes
FROM RiderShift a
JOIN RiderShift b
ON b.rider_id = a.rider_id
AND a.shift_id < b.shift_id
AND a.login_at < b.logout_at
AND b.login_at < a.logout_at
ORDER BY a.rider_id, first_shift, second_shiftAnother way
SELECT a.rider_id, a.shift_id AS first_shift, b.shift_id AS second_shift,
TIMESTAMPDIFF(MINUTE,
CASE WHEN a.login_at > b.login_at THEN a.login_at ELSE b.login_at END,
CASE WHEN a.logout_at < b.logout_at THEN a.logout_at ELSE b.logout_at END) AS overlap_minutes
FROM RiderShift a, RiderShift b
WHERE a.rider_id = b.rider_id AND a.shift_id < b.shift_id
AND NOT (a.logout_at <= b.login_at OR b.logout_at <= a.login_at)
ORDER BY 1, 2, 3← Next-Month Retention of First-Order Cohorts · Dark Store Daily Orders, Zero Days Included →