Double-Logged Rider Shifts That Overlap

Hard
168.

Double-Logged Rider Shifts That Overlap

HardJoinsDates

MySQL

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

ColumnType
shift_idint
rider_idint
login_atdatetime
logout_atdatetime

Primary key: shift_id.

One row per closed login session; logout_at is always later than login_at.

Example

The rows must come back in the order shown.

RiderShift

shift_idrider_idlogin_atlogout_at
1212024-08-05 08:00:002024-08-05 12:00:00
2212024-08-05 11:30:002024-08-05 15:00:00
3212024-08-05 15:00:002024-08-05 18:00:00
4222024-08-05 09:00:002024-08-05 17:00:00
5222024-08-05 10:00:002024-08-05 11:15:00
6222024-08-05 16:30:002024-08-05 20:00:00
7232024-08-05 10:00:002024-08-05 14:00:00
8212024-08-05 07:00:002024-08-05 08:00:00

Output

rider_idfirst_shiftsecond_shiftoverlap_minutes
211230
224575
224630
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.

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

ColumnType
shift_idint
rider_idint
login_atdatetime
logout_atdatetime

Primary key: shift_id.

One row per closed login session; logout_at is always later than login_at.

Examples

Example 1

RiderShift

shift_idrider_idlogin_atlogout_at
1212024-08-05 08:00:002024-08-05 12:00:00
2212024-08-05 11:30:002024-08-05 15:00:00
3212024-08-05 15:00:002024-08-05 18:00:00
4222024-08-05 09:00:002024-08-05 17:00:00
5222024-08-05 10:00:002024-08-05 11:15:00
6222024-08-05 16:30:002024-08-05 20:00:00
7232024-08-05 10:00:002024-08-05 14:00:00
8212024-08-05 07:00:002024-08-05 08:00:00

Output

rider_idfirst_shiftsecond_shiftoverlap_minutes
211230
224575
224630

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_shift

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