Swiggy: Orders That Stacked Two Platform Coupons
At checkout a customer may combine one platform coupon (funded by the app) with restaurant and bank-card offers, but never two platform coupons.
- Difficulty: Medium
- Topics: Aggregation, Strings
- Dialect: MySQL
- Problem: #959
Problem statement
At checkout a customer may combine one platform coupon (funded by the app) with restaurant and bank-card offers, but never two platform coupons. A checkout bug let some orders through with more, and the growth team needs the list to recover the money.
For every order that applied two or more coupons of type platform, return order_id, platform_coupons — the platform coupon codes in alphabetical order joined by a comma and a space (', ') — and platform_discount, the total discount of those platform coupons. Restaurant and payment coupons are ignored entirely. Order the rows by order_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: CouponUse
| Column | Type |
|---|---|
| order_id | int |
| coupon_code | varchar |
| coupon_type | enum(platform, restaurant, payment) |
| discount | int |
Primary key: order_id, coupon_code.
One row per coupon applied to an order, with the rupees it took off. Codes are upper-case letters and digits.
Examples
Example 1
CouponUse
| order_id | coupon_code | coupon_type | discount |
|---|---|---|---|
| 7001 | WELCOME50 | platform | 50 |
| 7001 | HDFC150 | payment | 150 |
| 7002 | SWIGGYIT | platform | 60 |
| 7002 | FLAT75 | platform | 75 |
| 7002 | BIRYANI20 | restaurant | 20 |
| 7003 | PARTY100 | platform | 100 |
| 7003 | DOSA10 | restaurant | 10 |
| 7004 | WELCOME50 | platform | 50 |
| 7004 | ONEPLUS | platform | 30 |
| 7004 | FLAT75 | platform | 75 |
Output
| order_id | platform_coupons | platform_discount |
|---|---|---|
| 7002 | FLAT75, SWIGGYIT | 135 |
| 7004 | FLAT75, ONEPLUS, WELCOME50 | 155 |
How to solve Swiggy: Orders That Stacked Two Platform Coupons
Only platform coupons matter, both for the rule and for the output, so the WHERE clause removes every other type first. What remains, grouped by order, is one group per order holding exactly its platform coupons. HAVING COUNT(*) >= 2 keeps the orders that broke the one-coupon rule, and the same group provides both output columns: SUM(discount) for the money at stake and GROUP_CONCAT(coupon_code ORDER BY coupon_code SEPARATOR ', ') for the readable list.
The order inside GROUP_CONCAT must be stated — without it the codes come out in whatever order the engine read the rows, and the string would differ from run to run. Filtering before grouping is also what makes the list and the sum correct: had the restaurant coupons been grouped in, they would appear in the string and inflate the discount.
The alternatives reach the same set of orders differently: a window COUNT(*) OVER (PARTITION BY order_id) tags every platform row with its order's platform count, or an IN subquery finds the offending orders first. All three scan the coupon table once or twice.
Reference solution (MySQL)
SELECT order_id,
GROUP_CONCAT(coupon_code ORDER BY coupon_code SEPARATOR ', ') AS platform_coupons,
SUM(discount) AS platform_discount
FROM CouponUse
WHERE coupon_type = 'platform'
GROUP BY order_id
HAVING COUNT(*) >= 2
ORDER BY order_idAnother way
SELECT order_id, GROUP_CONCAT(coupon_code ORDER BY coupon_code SEPARATOR ', ') AS platform_coupons, SUM(discount) AS platform_discount
FROM (SELECT order_id, coupon_code, discount, COUNT(*) OVER (PARTITION BY order_id) AS n
FROM CouponUse WHERE coupon_type = 'platform') p
WHERE n >= 2
GROUP BY order_id
ORDER BY order_idAnother way
SELECT c.order_id, GROUP_CONCAT(c.coupon_code ORDER BY c.coupon_code SEPARATOR ', ') AS platform_coupons, SUM(c.discount) AS platform_discount
FROM CouponUse c
WHERE c.coupon_type = 'platform'
AND c.order_id IN (SELECT order_id FROM CouponUse WHERE coupon_type = 'platform' GROUP BY order_id HAVING COUNT(*) > 1)
GROUP BY c.order_id
ORDER BY c.order_id← Swiggy: Swiggy One Plans That Paid for Themselves · Swiggy: Longest Idle Gap in a Partner's Day →