Swiggy: Share of Each Partner's Orders Delivered in a Batch

Medium
956.

Swiggy: Share of Each Partner's Orders Delivered in a Batch

To save rides, the dispatch system sometimes hands one delivery partner two or three nearby orders on a single trip — a batched trip. The fleet team wants to know how much of each partner's work came batched.

A trip is batched when it carried two or more orders. For every partner, return partner_id, total_orders, batched_orders (orders that rode on a batched trip) and batched_pct, the batched share as a percentage of the partner's orders rounded to one decimal place. Order the rows by partner_id.

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: Delivery

ColumnType
order_idint
partner_idint
trip_idint
delivered_atdatetime

Primary key: order_id.

One row per delivered order. trip_id names the trip that carried it; every order of a trip has the same partner.

Example

The rows must come back in the order shown.

Delivery

order_idpartner_idtrip_iddelivered_at
1712025-04-01 12:20:00
2712025-04-01 12:26:00
3722025-04-01 13:05:00
4732025-04-01 13:40:00
5842025-04-01 19:10:00
6842025-04-01 19:14:00
7842025-04-01 19:21:00
8952025-04-01 09:30:00
9962025-04-01 10:15:00
101072025-04-01 20:00:00
2 more rows

Output

partner_idtotal_ordersbatched_ordersbatched_pct
74250
833100
9200
103266.7
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: Share of Each Partner's Orders Delivered in a Batch

To save rides, the dispatch system sometimes hands one delivery partner two or three nearby orders on a single trip — a batched trip.

  • Difficulty: Medium
  • Topics: Aggregation, Conditional Logic, Subqueries
  • Dialect: MySQL
  • Problem: #956

Problem statement

To save rides, the dispatch system sometimes hands one delivery partner two or three nearby orders on a single trip — a batched trip. The fleet team wants to know how much of each partner's work came batched.

A trip is batched when it carried two or more orders. For every partner, return partner_id, total_orders, batched_orders (orders that rode on a batched trip) and batched_pct, the batched share as a percentage of the partner's orders rounded to one decimal place. Order the rows by partner_id.

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: Delivery

ColumnType
order_idint
partner_idint
trip_idint
delivered_atdatetime

Primary key: order_id.

One row per delivered order. trip_id names the trip that carried it; every order of a trip has the same partner.

Examples

Example 1

Delivery

order_idpartner_idtrip_iddelivered_at
1712025-04-01 12:20:00
2712025-04-01 12:26:00
3722025-04-01 13:05:00
4732025-04-01 13:40:00
5842025-04-01 19:10:00
6842025-04-01 19:14:00
7842025-04-01 19:21:00
8952025-04-01 09:30:00
9962025-04-01 10:15:00
101072025-04-01 20:00:00
111072025-04-01 20:09:00
121082025-04-01 21:02:00

Output

partner_idtotal_ordersbatched_ordersbatched_pct
74250
833100
9200
103266.7

How to solve Swiggy: Share of Each Partner's Orders Delivered in a Batch

The fact that decides each row — was this order batched? — lives one level up, on the trip. So the query works in two steps. First count orders per trip (trip_size); a trip with two or more is batched. Then join that count back to every order and aggregate per partner: COUNT(*) is the partner's orders, and a conditional sum SUM(CASE WHEN orders_on_trip >= 2 THEN 1 ELSE 0 END) counts the ones that rode on a batched trip.

The percentage is 100 * batched / total, rounded to one decimal at the end. MySQL's / is decimal division, so there is no integer truncation to guard against; rounding intermediate values would only add error. A partner who never batched gets 0.0 rather than disappearing, because the conditional sum adds zeros instead of filtering rows out.

The alternative replaces the grouped CTE with a window: COUNT(*) OVER (PARTITION BY trip_id) attaches the trip's size to each order row without collapsing anything, and the outer query sums the flags. Both read the deliveries twice at most and are linear apart from the grouping sort.

Reference solution (MySQL)

WITH trip_size AS (
  SELECT trip_id, COUNT(*) AS orders_on_trip FROM Delivery GROUP BY trip_id
)
SELECT d.partner_id,
       COUNT(*) AS total_orders,
       SUM(CASE WHEN t.orders_on_trip >= 2 THEN 1 ELSE 0 END) AS batched_orders,
       ROUND(100 * SUM(CASE WHEN t.orders_on_trip >= 2 THEN 1 ELSE 0 END) / COUNT(*), 1) AS batched_pct
FROM Delivery d
JOIN trip_size t ON t.trip_id = d.trip_id
GROUP BY d.partner_id
ORDER BY d.partner_id

Another way

SELECT partner_id, COUNT(*) AS total_orders, SUM(batched) AS batched_orders, ROUND(100 * SUM(batched) / COUNT(*), 1) AS batched_pct
FROM (SELECT partner_id, IF(COUNT(*) OVER (PARTITION BY trip_id) >= 2, 1, 0) AS batched FROM Delivery) x
GROUP BY partner_id
ORDER BY partner_id

← Swiggy: Kitchens That Overshot the Promised Prep Time · Swiggy: Instamart SKUs Out of Stock Across a Whole City →