Swiggy: Orders That Stacked Two Platform Coupons

Medium
959.

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

ColumnTypeValues
order_idint
coupon_codevarchar
coupon_typeenumplatform, restaurant, payment
discountint

Primary key (together): 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.

Example

The rows must come back in the order shown.

CouponUse

order_idcoupon_codecoupon_typediscount
7001WELCOME50platform50
7001HDFC150payment150
7002SWIGGYITplatform60
7002FLAT75platform75
7002BIRYANI20restaurant20
7003PARTY100platform100
7003DOSA10restaurant10
7004WELCOME50platform50
7004ONEPLUSplatform30
7004FLAT75platform75

Output

order_idplatform_couponsplatform_discount
7002FLAT75, SWIGGYIT135
7004FLAT75, ONEPLUS, WELCOME50155
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: 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

ColumnType
order_idint
coupon_codevarchar
coupon_typeenum(platform, restaurant, payment)
discountint

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_idcoupon_codecoupon_typediscount
7001WELCOME50platform50
7001HDFC150payment150
7002SWIGGYITplatform60
7002FLAT75platform75
7002BIRYANI20restaurant20
7003PARTY100platform100
7003DOSA10restaurant10
7004WELCOME50platform50
7004ONEPLUSplatform30
7004FLAT75platform75

Output

order_idplatform_couponsplatform_discount
7002FLAT75, SWIGGYIT135
7004FLAT75, ONEPLUS, WELCOME50155

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_id

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

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