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
| Column | Type |
|---|---|
| sku_id | int |
| sku_name | varchar |
| aisle | int |
Primary key: sku_id.
One row per catalogue item with the aisle it sits in. Names are unique.
Table: OrderLine
| Column | Type |
|---|---|
| order_id | int |
| sku_id | int |
| qty | int |
Primary key: order_id, sku_id.
One row per item in an order; qty is between 1 and 12.
Examples
Example 1
Sku
| sku_id | sku_name | aisle |
|---|---|---|
| 1 | Amul Butter | 2 |
| 2 | Bread Loaf | 1 |
| 3 | Eggs Tray | 2 |
| 4 | Toned Milk | 3 |
| 5 | Maggi Noodles | 1 |
OrderLine
| order_id | sku_id | qty |
|---|---|---|
| 101 | 2 | 1 |
| 101 | 4 | 3 |
| 101 | 3 | 2 |
| 101 | 5 | 2 |
| 102 | 1 | 4 |
| 103 | 4 | 1 |
Output
| order_id | bag_no | units | bag_items |
|---|---|---|---|
| 101 | 1 | 4 | Bread Loaf, Eggs Tray, Maggi Noodles |
| 101 | 2 | 4 | Eggs Tray, Toned Milk |
| 102 | 1 | 4 | Amul Butter |
| 103 | 1 | 1 | Toned 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_noAnother 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 →