Swiggy: Instamart Picking Bags From Order Lines

Hard
972.

Swiggy: Instamart Picking Bags From Order Lines

In an Instamart dark store a picker walks the aisles for one order and drops every unit into a delivery bag; a bag holds 4 units, and a new bag is opened when the current one is full. The store-ops team wants the bag-by-bag packing list printed for the picker.

An order line of qty N is N separate units. Units are picked in order of the SKU's aisle, then sku_name, and the units of one line one after another. The 1st to 4th units picked go into bag 1, the 5th to 8th into bag 2, and so on. For every order and bag, return order_id, bag_no, units (units in the bag) and bag_items, the distinct SKU names in the bag in alphabetical order joined by ', '. Order the rows by order_id, then bag_no.

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

ColumnType
sku_idint
sku_namevarchar
aisleint

Primary key: sku_id.

One row per catalogue item with the aisle it sits in. Names are unique.

Table: OrderLine

ColumnType
order_idint
sku_idint
qtyint

Primary key (together): order_id, sku_id.

One row per item in an order; qty is between 1 and 12.

Example

The rows must come back in the order shown.

Sku

sku_idsku_nameaisle
1Amul Butter2
2Bread Loaf1
3Eggs Tray2
4Toned Milk3
5Maggi Noodles1

OrderLine

order_idsku_idqty
10121
10143
10132
10152
10214
10341

Output

order_idbag_nounitsbag_items
10114Bread Loaf, Eggs Tray, Maggi Noodles
10124Eggs Tray, Toned Milk
10214Amul Butter
10311Toned Milk
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: Instamart Picking Bags From Order Lines

In an Instamart dark store a picker walks the aisles for one order and drops every unit into a delivery bag; a bag holds 4 units, and a new bag is opened…

  • Difficulty: Hard
  • Topics: Window Functions, Joins, Aggregation
  • Dialect: MySQL
  • Problem: #972

Problem statement

In an Instamart dark store a picker walks the aisles for one order and drops every unit into a delivery bag; a bag holds 4 units, and a new bag is opened when the current one is full. The store-ops team wants the bag-by-bag packing list printed for the picker.

An order line of qty N is N separate units. Units are picked in order of the SKU's aisle, then sku_name, and the units of one line one after another. The 1st to 4th units picked go into bag 1, the 5th to 8th into bag 2, and so on. For every order and bag, return order_id, bag_no, units (units in the bag) and bag_items, the distinct SKU names in the bag in alphabetical order joined by ', '. Order the rows by order_id, then bag_no.

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

ColumnType
sku_idint
sku_namevarchar
aisleint

Primary key: sku_id.

One row per catalogue item with the aisle it sits in. Names are unique.

Table: OrderLine

ColumnType
order_idint
sku_idint
qtyint

Primary key: order_id, sku_id.

One row per item in an order; qty is between 1 and 12.

Examples

Example 1

Sku

sku_idsku_nameaisle
1Amul Butter2
2Bread Loaf1
3Eggs Tray2
4Toned Milk3
5Maggi Noodles1

OrderLine

order_idsku_idqty
10121
10143
10132
10152
10214
10341

Output

order_idbag_nounitsbag_items
10114Bread Loaf, Eggs Tray, Maggi Noodles
10124Eggs Tray, Toned Milk
10214Amul Butter
10311Toned Milk

How to solve Swiggy: Instamart Picking Bags From Order Lines

The rule is stated per unit, but the data is per line, so the first step expands quantities into rows. A small numbers table — here nums, the numbers 1 to 12 from a recursive CTE — is joined to every line with n.k <= l.qty, which turns a line of quantity 3 into three rows and a line of quantity 1 into one. Any table with enough consecutive integers would do; the recursion just saves typing them.

With one row per unit, the picking order is a ROW_NUMBER() per order over aisle, sku_name, k — the unit number keeps a line's units together and makes the numbering total, so it never depends on storage order. Bags then fall out of integer arithmetic: (pick_no - 1) DIV 4 + 1 maps units 1–4 to bag 1, 5–8 to bag 2. Grouping by order and bag gives the unit count, and GROUP_CONCAT(DISTINCT sku_name ORDER BY sku_name …) lists each SKU once even when several of its units share a bag; a line split across two bags appears in both. pick_no - 1 is never negative, so the subtraction is safe on MySQL's unsigned ROW_NUMBER().

The alternative never expands units. A running SUM(qty) gives each line the range of pick positions it occupies, (upto − qty, upto]; joining the lines to bag numbers whose range ((b − 1)·4, b·4] overlaps it, and measuring the overlap with LEAST/GREATEST, gives the units of that line in that bag. It does less work for large quantities; the expansion is easier to read and to extend (for example to per-unit weights).

Reference solution (MySQL)

WITH RECURSIVE nums AS (
  SELECT 1 AS k
  UNION ALL
  SELECT k + 1 FROM nums WHERE k < 12
), units AS (
  SELECT l.order_id, s.sku_name,
         ROW_NUMBER() OVER (PARTITION BY l.order_id ORDER BY s.aisle, s.sku_name, n.k) AS pick_no
  FROM OrderLine l
  JOIN Sku s ON s.sku_id = l.sku_id
  JOIN nums n ON n.k <= l.qty
)
SELECT order_id, (pick_no - 1) DIV 4 + 1 AS bag_no, COUNT(*) AS units,
       GROUP_CONCAT(DISTINCT sku_name ORDER BY sku_name SEPARATOR ', ') AS bag_items
FROM units
GROUP BY order_id, bag_no
ORDER BY order_id, bag_no

Another way

WITH cum AS (
  SELECT l.order_id, s.sku_name, l.qty,
         SUM(l.qty) OVER (PARTITION BY l.order_id ORDER BY s.aisle, s.sku_name ROWS UNBOUNDED PRECEDING) AS upto
  FROM OrderLine l JOIN Sku s ON s.sku_id = l.sku_id
), bags AS (
  SELECT 1 AS b UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
  UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12
), pieces AS (
  SELECT c.order_id, g.b AS bag_no, c.sku_name,
         LEAST(c.upto, g.b * 4) - GREATEST(c.upto - c.qty, (g.b - 1) * 4) AS n
  FROM cum c JOIN bags g
    ON (g.b - 1) * 4 < c.upto AND g.b * 4 > c.upto - c.qty
)
SELECT order_id, bag_no, SUM(n) AS units,
       GROUP_CONCAT(sku_name ORDER BY sku_name SEPARATOR ', ') AS bag_items
FROM pieces
GROUP BY order_id, bag_no
ORDER BY order_id, bag_no

← Swiggy: Instamart Items Most Often Bought Together · Uber: Uber One Trials Ending Without Auto-Renew →