Swiggy: Units Sold per Item From Instamart Combo Codes

Hard
969.

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

ColumnTypeValues
sale_idint
store_idint
combo_codevarchar
packsint
statusenumsold, 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.

Example

The rows must come back in the order shown.

ComboSale

sale_idstore_idcombo_codepacksstatus
11MILK:2|BREAD:1|EGGS:61sold
21MILK:1|MAGGI:42sold
31BREAD:23sold
41EGGS:6|BUTTER:11returned
52ATTA:1|CURD:2|MILK:2|ONION:32sold
62CURD:11sold
73MILK:14returned

Output

store_iditem_codeunits_sold
1BREAD7
1EGGS6
1MAGGI8
1MILK4
2ATTA2
2CURD5
2MILK4
2ONION6
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: 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

ColumnType
sale_idint
store_idint
combo_codevarchar
packsint
statusenum(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_idstore_idcombo_codepacksstatus
11MILK:2|BREAD:1|EGGS:61sold
21MILK:1|MAGGI:42sold
31BREAD:23sold
41EGGS:6|BUTTER:11returned
52ATTA:1|CURD:2|MILK:2|ONION:32sold
62CURD:11sold
73MILK:14returned

Output

store_iditem_codeunits_sold
1BREAD7
1EGGS6
1MAGGI8
1MILK4
2ATTA2
2CURD5
2MILK4
2ONION6

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_code

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