Swiggy: Instamart Items Most Often Bought Together

Hard
971.

Swiggy: Instamart Items Most Often Bought Together

The Instamart merchandising team builds "frequently bought together" combos from basket data: which two items land in the same order most often.

For every pair of different SKUs, count the orders that contain both. Consider only pairs bought together in at least two orders, and return the pairs whose count ranks in the top 3 — rank by count with ties sharing a rank and the next rank skipping (as RANK() does), so every pair tied at rank 3 is included. Return sku_a and sku_b (the two names, sku_a alphabetically first) and orders_together. Order by orders_together descending, then sku_a, then sku_b.

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

Primary key: sku_id.

One row per catalogue item; 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 — an item appears at most once per order, with its quantity.

Example

The rows must come back in the order shown.

Sku

sku_idsku_name
1Amul Butter
2Bread Loaf
3Eggs Tray
4Maggi Noodles
5Onion Pack

OrderLine

order_idsku_idqty
111
121
131
212
221
321
331
411
422
443
9 more rows

Output

sku_asku_borders_together
Amul ButterBread Loaf3
Bread LoafEggs Tray3
Amul ButterEggs Tray2
Maggi NoodlesOnion Pack2
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 Items Most Often Bought Together

The Instamart merchandising team builds "frequently bought together" combos from basket data: which two items land in the same order most often.

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

Problem statement

The Instamart merchandising team builds "frequently bought together" combos from basket data: which two items land in the same order most often.

For every pair of different SKUs, count the orders that contain both. Consider only pairs bought together in at least two orders, and return the pairs whose count ranks in the top 3 — rank by count with ties sharing a rank and the next rank skipping (as RANK() does), so every pair tied at rank 3 is included. Return sku_a and sku_b (the two names, sku_a alphabetically first) and orders_together. Order by orders_together descending, then sku_a, then sku_b.

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

Primary key: sku_id.

One row per catalogue item; names are unique.

Table: OrderLine

ColumnType
order_idint
sku_idint
qtyint

Primary key: order_id, sku_id.

One row per item in an order — an item appears at most once per order, with its quantity.

Examples

Example 1

Sku

sku_idsku_name
1Amul Butter
2Bread Loaf
3Eggs Tray
4Maggi Noodles
5Onion Pack

OrderLine

order_idsku_idqty
111
121
131
212
221
321
331
411
422
443
531
542
551
641
652
721
732
811
831

Output

sku_asku_borders_together
Amul ButterBread Loaf3
Bread LoafEggs Tray3
Amul ButterEggs Tray2
Maggi NoodlesOnion Pack2

How to solve Swiggy: Instamart Items Most Often Bought Together

Pairs of items in the same basket come from a self join of OrderLine on order_id. Joining without a condition on the items would pair every item with itself and count every pair twice, (A, B) and (B, A); requiring b.sku_id > a.sku_id keeps each unordered pair exactly once. Grouping by the pair then counts the orders containing both — each order contributes one joined row per pair, because an item appears at most once per order — and HAVING COUNT(*) >= 2 drops pairs seen in a single basket.

The names are attached afterwards and put in alphabetical order with two CASE expressions, since the smaller id is not necessarily the alphabetically first name. Ranking uses RANK(), which gives tied counts the same rank and skips the next one, so pos <= 3 returns every pair tied at the boundary — as asked — rather than an arbitrary three.

The alternative pairs by name directly (s1.sku_name < s2.sku_name, which both deduplicates and orders the pair) and replaces the window with its definition: a pair is in the top 3 when fewer than three pairs have a strictly larger count. That correlated count is exactly RANK() - 1. The self join is the expensive part — quadratic in basket size — which is why real systems cap basket length or sample orders.

Reference solution (MySQL)

WITH pairs AS (
  SELECT a.sku_id AS x, b.sku_id AS y, COUNT(*) AS orders_together
  FROM OrderLine a
  JOIN OrderLine b ON b.order_id = a.order_id AND b.sku_id > a.sku_id
  GROUP BY a.sku_id, b.sku_id
  HAVING COUNT(*) >= 2
), named AS (
  SELECT CASE WHEN s1.sku_name < s2.sku_name THEN s1.sku_name ELSE s2.sku_name END AS sku_a,
         CASE WHEN s1.sku_name < s2.sku_name THEN s2.sku_name ELSE s1.sku_name END AS sku_b,
         p.orders_together,
         RANK() OVER (ORDER BY p.orders_together DESC) AS pos
  FROM pairs p
  JOIN Sku s1 ON s1.sku_id = p.x
  JOIN Sku s2 ON s2.sku_id = p.y
)
SELECT sku_a, sku_b, orders_together
FROM named
WHERE pos <= 3
ORDER BY orders_together DESC, sku_a, sku_b

Another way

WITH pairs AS (
  SELECT s1.sku_name AS sku_a, s2.sku_name AS sku_b, COUNT(*) AS orders_together
  FROM OrderLine a
  JOIN OrderLine b ON b.order_id = a.order_id
  JOIN Sku s1 ON s1.sku_id = a.sku_id
  JOIN Sku s2 ON s2.sku_id = b.sku_id
  WHERE s1.sku_name < s2.sku_name
  GROUP BY s1.sku_name, s2.sku_name
  HAVING COUNT(*) >= 2
)
SELECT p.sku_a, p.sku_b, p.orders_together
FROM pairs p
WHERE (SELECT COUNT(*) FROM pairs q WHERE q.orders_together > p.orders_together) < 3
ORDER BY p.orders_together DESC, p.sku_a, p.sku_b

← Swiggy: Kitchens Slowing Down Three Weeks Running · Swiggy: Instamart Picking Bags From Order Lines →