Swiggy: Longest Idle Gap in a Partner's Day — SQL Problem
Between dropping one order and picking up the next, a delivery partner earns nothing.
- Difficulty: Medium
- Topics: Window Functions, Dates
- Dialect: MySQL
- Problem: #960
Problem statement
Between dropping one order and picking up the next, a delivery partner earns nothing. The partner-experience team wants each partner's worst wait of the day to tune where idle riders are sent.
Within one partner's day (the date of picked_up_at), the idle gap after a trip is the minutes from its delivered_at to the picked_up_at of the next trip of that partner on the same day. For every partner and day with at least two trips, return partner_id, shift_date and longest_idle_minutes, the largest such gap (0 when a trip started the moment the previous one ended). Times are recorded to the minute and a partner's trips never overlap or cross midnight. Return the rows in any order.
A practice scenario set at Swiggy: the tables and data are invented, and this is not a question from, or endorsed by, Swiggy.
Tables
Table: PartnerTrip
| Column | Type |
|---|---|
| trip_id | int |
| partner_id | int |
| picked_up_at | datetime |
| delivered_at | datetime |
Primary key: trip_id.
One row per trip: the food is picked up from the restaurant, then delivered. Trip ids are not in time order.
Examples
Example 1
PartnerTrip
| trip_id | partner_id | picked_up_at | delivered_at |
|---|---|---|---|
| 14 | 21 | 2025-04-01 13:05:00 | 2025-04-01 13:30:00 |
| 11 | 21 | 2025-04-01 12:00:00 | 2025-04-01 12:25:00 |
| 12 | 21 | 2025-04-01 12:40:00 | 2025-04-01 13:05:00 |
| 17 | 21 | 2025-04-01 14:10:00 | 2025-04-01 14:30:00 |
| 15 | 21 | 2025-04-02 09:00:00 | 2025-04-02 09:20:00 |
| 16 | 22 | 2025-04-01 18:30:00 | 2025-04-01 18:50:00 |
| 13 | 22 | 2025-04-01 18:00:00 | 2025-04-01 18:30:00 |
Output
| partner_id | shift_date | longest_idle_minutes |
|---|---|---|
| 21 | 2025-04-01 | 40 |
| 22 | 2025-04-01 | 0 |
How to solve Swiggy: Longest Idle Gap in a Partner's Day
The gap is a relation between consecutive rows, which is what LEAD is for. Partition by partner and by the day of the pickup, order by picked_up_at, and LEAD(picked_up_at) gives each trip the start of the one after it. TIMESTAMPDIFF(MINUTE, delivered_at, next_pickup) is then the idle time after that trip; it is 0 when the partner picked up the next order the minute they dropped the last.
The last trip of every day has no following row, so LEAD returns NULL and so does the gap. Filtering idle IS NOT NULL before grouping removes those rows, and as a side effect a day with a single trip has no rows left and drops out — exactly the "at least two trips" rule. MAX(idle) per partner and day is the answer. Trip ids are deliberately shuffled, so ordering by id would be wrong; only the timestamps give the sequence.
The alternative finds the next pickup with a correlated MIN(picked_up_at) over later trips of the same partner and day, and uses HAVING COUNT(*) >= 2 for the rule (MAX ignores the last trip's NULL). It is quadratic per partner-day without an index; the window version sorts once.
Reference solution (MySQL)
WITH gaps AS (
SELECT partner_id, DATE(picked_up_at) AS shift_date,
TIMESTAMPDIFF(MINUTE, delivered_at,
LEAD(picked_up_at) OVER (PARTITION BY partner_id, DATE(picked_up_at) ORDER BY picked_up_at)) AS idle
FROM PartnerTrip
)
SELECT partner_id, shift_date, MAX(idle) AS longest_idle_minutes
FROM gaps
WHERE idle IS NOT NULL
GROUP BY partner_id, shift_dateAnother way
SELECT a.partner_id, DATE(a.picked_up_at) AS shift_date,
MAX(TIMESTAMPDIFF(MINUTE, a.delivered_at,
(SELECT MIN(b.picked_up_at) FROM PartnerTrip b
WHERE b.partner_id = a.partner_id AND DATE(b.picked_up_at) = DATE(a.picked_up_at)
AND b.picked_up_at > a.picked_up_at))) AS longest_idle_minutes
FROM PartnerTrip a
GROUP BY a.partner_id, DATE(a.picked_up_at)
HAVING COUNT(*) >= 2← Swiggy: Orders That Stacked Two Platform Coupons · Swiggy: Food Customers Who Later Tried Instamart →