Swiggy: Partner Top-Up to the Shift Earnings Guarantee

Medium
964.

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

ColumnType
shift_idint
partner_idint
shift_datedate
login_minutesint
guaranteeint

Primary key: shift_id.

One row per shift a partner worked; guarantee is the minimum payout promised for it, in rupees.

Table: ShiftOrder

ColumnType
order_idint
shift_idint
payoutint
tipint

Primary key: order_id.

One row per order delivered during a shift. tip is NULL when the customer left none.

Example

The rows must come back in the order shown.

PartnerShift

shift_idpartner_idshift_datelogin_minutesguarantee
1312025-08-01480900
2322025-08-01419900
3332025-08-01420800
4312025-08-02600900
5342025-08-02450700

ShiftOrder

order_idshift_idpayouttip
11300NULL
2125020
31200NULL
42100NULL
535000
63350NULL
75400200

Output

shift_idpartner_idearningstop_up
131750150
4310900
534400300
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: 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

ColumnType
shift_idint
partner_idint
shift_datedate
login_minutesint
guaranteeint

Primary key: shift_id.

One row per shift a partner worked; guarantee is the minimum payout promised for it, in rupees.

Table: ShiftOrder

ColumnType
order_idint
shift_idint
payoutint
tipint

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_idpartner_idshift_datelogin_minutesguarantee
1312025-08-01480900
2322025-08-01419900
3332025-08-01420800
4312025-08-02600900
5342025-08-02450700

ShiftOrder

order_idshift_idpayouttip
11300NULL
2125020
31200NULL
42100NULL
535000
63350NULL
75400200

Output

shift_idpartner_idearningstop_up
131750150
4310900
534400300

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_id

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