Swiggy: Longest Idle Gap in a Partner's Day

Medium
960.

Swiggy: Longest Idle Gap in a Partner's Day

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

ColumnType
trip_idint
partner_idint
picked_up_atdatetime
delivered_atdatetime

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.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

PartnerTrip

trip_idpartner_idpicked_up_atdelivered_at
14212025-04-01 13:05:002025-04-01 13:30:00
11212025-04-01 12:00:002025-04-01 12:25:00
12212025-04-01 12:40:002025-04-01 13:05:00
17212025-04-01 14:10:002025-04-01 14:30:00
15212025-04-02 09:00:002025-04-02 09:20:00
16222025-04-01 18:30:002025-04-01 18:50:00
13222025-04-01 18:00:002025-04-01 18:30:00

Output

partner_idshift_datelongest_idle_minutes
212025-04-0140
222025-04-010
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.

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

ColumnType
trip_idint
partner_idint
picked_up_atdatetime
delivered_atdatetime

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_idpartner_idpicked_up_atdelivered_at
14212025-04-01 13:05:002025-04-01 13:30:00
11212025-04-01 12:00:002025-04-01 12:25:00
12212025-04-01 12:40:002025-04-01 13:05:00
17212025-04-01 14:10:002025-04-01 14:30:00
15212025-04-02 09:00:002025-04-02 09:20:00
16222025-04-01 18:30:002025-04-01 18:50:00
13222025-04-01 18:00:002025-04-01 18:30:00

Output

partner_idshift_datelongest_idle_minutes
212025-04-0140
222025-04-010

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_date

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