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
| Column | Type |
|---|---|
| order_id | int |
| partner_id | int |
| trip_id | int |
| delivered_at | datetime |
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_id | partner_id | trip_id | delivered_at |
|---|---|---|---|
| 1 | 7 | 1 | 2025-04-01 12:20:00 |
| 2 | 7 | 1 | 2025-04-01 12:26:00 |
| 3 | 7 | 2 | 2025-04-01 13:05:00 |
| 4 | 7 | 3 | 2025-04-01 13:40:00 |
| 5 | 8 | 4 | 2025-04-01 19:10:00 |
| 6 | 8 | 4 | 2025-04-01 19:14:00 |
| 7 | 8 | 4 | 2025-04-01 19:21:00 |
| 8 | 9 | 5 | 2025-04-01 09:30:00 |
| 9 | 9 | 6 | 2025-04-01 10:15:00 |
| 10 | 10 | 7 | 2025-04-01 20:00:00 |
| 11 | 10 | 7 | 2025-04-01 20:09:00 |
| 12 | 10 | 8 | 2025-04-01 21:02:00 |
Output
| partner_id | total_orders | batched_orders | batched_pct |
|---|---|---|---|
| 7 | 4 | 2 | 50 |
| 8 | 3 | 3 | 100 |
| 9 | 2 | 0 | 0 |
| 10 | 3 | 2 | 66.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_idAnother 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 →