Swiggy: Partner Top-Up to the Shift Earnings Guarantee
To keep riders on the road during slow days, a delivery partner who stays logged in for a long enough shift is guaranteed a minimum payout; if the orders of…
- Difficulty: Medium
- Topics: Joins, Aggregation, Conditional Logic
- Dialect: MySQL
- Problem: #964
Problem statement
To keep riders on the road during slow days, a delivery partner who stays logged in for a long enough shift is guaranteed a minimum payout; if the orders of the shift paid less, the company tops it up.
A shift is eligible when login_minutes is at least 420. Its earnings are the sum of payout over its orders — tips are excluded, they belong to the partner on top of everything — and 0 for a shift with no orders. For every eligible shift whose earnings fall short of its guarantee, return shift_id, partner_id, earnings and top_up (guarantee minus earnings). Order the rows by shift_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: PartnerShift
| Column | Type |
|---|---|
| shift_id | int |
| partner_id | int |
| shift_date | date |
| login_minutes | int |
| guarantee | int |
Primary key: shift_id.
One row per shift a partner worked; guarantee is the minimum payout promised for it, in rupees.
Table: ShiftOrder
| Column | Type |
|---|---|
| order_id | int |
| shift_id | int |
| payout | int |
| tip | int |
Primary key: order_id.
One row per order delivered during a shift. tip is NULL when the customer left none.
Examples
Example 1
PartnerShift
| shift_id | partner_id | shift_date | login_minutes | guarantee |
|---|---|---|---|---|
| 1 | 31 | 2025-08-01 | 480 | 900 |
| 2 | 32 | 2025-08-01 | 419 | 900 |
| 3 | 33 | 2025-08-01 | 420 | 800 |
| 4 | 31 | 2025-08-02 | 600 | 900 |
| 5 | 34 | 2025-08-02 | 450 | 700 |
ShiftOrder
| order_id | shift_id | payout | tip |
|---|---|---|---|
| 1 | 1 | 300 | NULL |
| 2 | 1 | 250 | 20 |
| 3 | 1 | 200 | NULL |
| 4 | 2 | 100 | NULL |
| 5 | 3 | 500 | 0 |
| 6 | 3 | 350 | NULL |
| 7 | 5 | 400 | 200 |
Output
| shift_id | partner_id | earnings | top_up |
|---|---|---|---|
| 1 | 31 | 750 | 150 |
| 4 | 31 | 0 | 900 |
| 5 | 34 | 400 | 300 |
How to solve Swiggy: Partner Top-Up to the Shift Earnings Guarantee
Every eligible shift has to be judged, including a shift in which the partner stayed logged in all day and got no orders — that is the case the guarantee exists for. A LEFT JOIN from shifts to orders keeps such a shift with one NULL-extended row, and COALESCE(SUM(o.payout), 0) turns its NULL sum into 0 earnings, so its top-up is the full guarantee. An inner join would silently lose it.
Eligibility is a property of the shift row, so it goes in WHERE (login_minutes >= 420, with 420 itself eligible). The shortfall test is a property of the group and goes in HAVING; writing the expression out rather than its alias keeps it portable. Only payout is summed: tips are the customer's gift to the rider and do not reduce what the company owes, and their NULLs never enter the arithmetic.
The alternative computes earnings with a correlated scalar subquery per shift (IFNULL handling the empty case) and filters in an outer query. It reads more like the business rule; the join-and-group version does the same work in one grouped pass over the orders.
Reference solution (MySQL)
SELECT s.shift_id, s.partner_id,
COALESCE(SUM(o.payout), 0) AS earnings,
s.guarantee - COALESCE(SUM(o.payout), 0) AS top_up
FROM PartnerShift s
LEFT JOIN ShiftOrder o ON o.shift_id = s.shift_id
WHERE s.login_minutes >= 420
GROUP BY s.shift_id, s.partner_id, s.guarantee
HAVING s.guarantee - COALESCE(SUM(o.payout), 0) > 0
ORDER BY s.shift_idAnother way
SELECT shift_id, partner_id, earnings, guarantee - earnings AS top_up FROM (
SELECT s.shift_id, s.partner_id, s.guarantee,
(SELECT IFNULL(SUM(o.payout), 0) FROM ShiftOrder o WHERE o.shift_id = s.shift_id) AS earnings
FROM PartnerShift s WHERE s.login_minutes >= 420
) t
WHERE earnings < guarantee
ORDER BY shift_id← Swiggy: Busiest Instamart Store per City Each Month · Swiggy: Swiggy Money Debits That Overdrew the Wallet →