Swiggy: Units Sold per Item From Instamart Combo Codes
Instamart sells combo packs — a breakfast pack, a party pack — and the point-of-sale system logs each sale with a packed combo code instead of one row per…
- Difficulty: Hard
- Topics: Strings, Subqueries, Aggregation
- Dialect: MySQL
- Problem: #969
Problem statement
Instamart sells combo packs — a breakfast pack, a party pack — and the point-of-sale system logs each sale with a packed combo code instead of one row per item. The replenishment team needs plain item counts per store to reorder stock.
A combo_code lists the items of one pack as ITEM:units entries separated by |, for example MILK:2|BREAD:1|EGGS:6; a code has one to four entries and never repeats an item. A sale of packs combo packs moves units × packs of every item in its code. Considering only sales with status sold (returns are ignored), return store_id, item_code and units_sold, the total units of each item that left each store. Order the rows by store_id, then item_code.
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: ComboSale
| Column | Type |
|---|---|
| sale_id | int |
| store_id | int |
| combo_code | varchar |
| packs | int |
| status | enum(sold, returned) |
Primary key: sale_id.
One row per combo sale at a dark store. Item codes are upper-case letters; units is a whole number from 1 to 9.
Examples
Example 1
ComboSale
| sale_id | store_id | combo_code | packs | status |
|---|---|---|---|---|
| 1 | 1 | MILK:2|BREAD:1|EGGS:6 | 1 | sold |
| 2 | 1 | MILK:1|MAGGI:4 | 2 | sold |
| 3 | 1 | BREAD:2 | 3 | sold |
| 4 | 1 | EGGS:6|BUTTER:1 | 1 | returned |
| 5 | 2 | ATTA:1|CURD:2|MILK:2|ONION:3 | 2 | sold |
| 6 | 2 | CURD:1 | 1 | sold |
| 7 | 3 | MILK:1 | 4 | returned |
Output
| store_id | item_code | units_sold |
|---|---|---|
| 1 | BREAD | 7 |
| 1 | EGGS | 6 |
| 1 | MAGGI | 8 |
| 1 | MILK | 4 |
| 2 | ATTA | 2 |
| 2 | CURD | 5 |
| 2 | MILK | 4 |
| 2 | ONION | 6 |
How to solve Swiggy: Units Sold per Item From Instamart Combo Codes
The data is packed into one string per sale, so the work is to unpack it into rows and then aggregate normally. The recursive CTE does it one entry at a time: the anchor takes the first entry with SUBSTRING_INDEX(combo_code, '|', 1) and keeps everything after the first | as rest (or '' when there is no |); the recursive step repeats the same on rest and stops when rest is empty. Every sale therefore produces exactly as many rows as its code has entries. Returned sales are filtered in the anchor, so they never enter the recursion.
Each entry is then split on : — SUBSTRING_INDEX(part, ':', 1) is the item, SUBSTRING_INDEX(part, ':', -1) the units, cast to a number — and multiplied by packs. Grouping by store and item adds up the same item across different combos, which is the whole point: MILK from a breakfast pack and from a tea-time pack are one shelf.
The alternative avoids recursion with a tiny numbers table: joining each sale to 1–4, keeping k up to its entry count (one more than the number of |, computed as a length difference after REPLACE), and taking the k-th entry with a nested SUBSTRING_INDEX. It relies on the four-entry limit; the recursive version works for any length. Both are linear in the total number of entries.
Reference solution (MySQL)
WITH RECURSIVE parts AS (
SELECT sale_id, store_id, packs,
SUBSTRING_INDEX(combo_code, '|', 1) AS part,
IF(LOCATE('|', combo_code) > 0, SUBSTRING(combo_code, LOCATE('|', combo_code) + 1), '') AS rest
FROM ComboSale
WHERE status = 'sold'
UNION ALL
SELECT sale_id, store_id, packs,
SUBSTRING_INDEX(rest, '|', 1),
IF(LOCATE('|', rest) > 0, SUBSTRING(rest, LOCATE('|', rest) + 1), '')
FROM parts
WHERE rest <> ''
)
SELECT store_id, SUBSTRING_INDEX(part, ':', 1) AS item_code,
SUM(CAST(SUBSTRING_INDEX(part, ':', -1) AS SIGNED) * packs) AS units_sold
FROM parts
GROUP BY store_id, item_code
ORDER BY store_id, item_codeAnother way
SELECT store_id, SUBSTRING_INDEX(part, ':', 1) AS item_code,
SUM(CAST(SUBSTRING_INDEX(part, ':', -1) AS SIGNED) * packs) AS units_sold
FROM (
SELECT s.store_id, s.packs,
SUBSTRING_INDEX(SUBSTRING_INDEX(s.combo_code, '|', n.k), '|', -1) AS part
FROM ComboSale s
JOIN (SELECT 1 AS k UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) n
ON n.k <= 1 + CHAR_LENGTH(s.combo_code) - CHAR_LENGTH(REPLACE(s.combo_code, '|', ''))
WHERE s.status = 'sold'
) p
GROUP BY store_id, item_code
ORDER BY store_id, item_code← Swiggy: GMV Report With City Subtotals and a Grand Total · Swiggy: Kitchens Slowing Down Three Weeks Running →