Products Most Often Bought in the Same Basket — SQL Problem
For the next planogram, the category team wants product pairs that customers buy together on the same bill.
- Difficulty: Hard
- Topics: Joins, Aggregation, Strings
- Dialect: MySQL
- Problem: #397
Problem statement
For the next planogram, the category team wants product pairs that customers buy together on the same bill. A pair is two different SKUs on one bill (quantities do not matter), counted once per bill. Only pairs seen together on at least 2 bills are interesting.
Return, for each such pair, pair (the two product names joined as <name of the smaller SKU> + <name of the larger SKU>, comparing the SKU codes), bills_together and support_pct = 100 × bills_together / (number of distinct bills in BillLine), rounded to 2 decimal places. Order the rows by bills_together highest first, then by pair.
Tables
Table: BasketProduct
| Column | Type |
|---|---|
| sku | varchar |
| product_name | varchar |
Primary key: sku.
Product names are unique.
Table: BillLine
| Column | Type |
|---|---|
| bill_id | int |
| sku | varchar |
| qty | int |
Primary key: bill_id, sku.
One row per SKU on a bill; every sku is in BasketProduct.
Examples
Example 1
BasketProduct
| sku | product_name |
|---|---|
| BEV-3001 | Masala Chai 250g |
| DAI-2002 | Paneer 200g |
| GRO-1002 | Toor Dal 1kg |
| SNK-6001 | Masala Chips 90g |
| GRO-1001 | Basmati Rice 5kg |
BillLine
| bill_id | sku | qty |
|---|---|---|
| 1 | BEV-3001 | 1 |
| 1 | SNK-6001 | 2 |
| 1 | GRO-1002 | 1 |
| 2 | BEV-3001 | 2 |
| 2 | SNK-6001 | 1 |
| 3 | GRO-1001 | 1 |
| 3 | GRO-1002 | 2 |
| 4 | GRO-1002 | 1 |
| 4 | GRO-1001 | 1 |
| 5 | DAI-2002 | 3 |
Output
| pair | bills_together | support_pct |
|---|---|---|
| Basmati Rice 5kg + Toor Dal 1kg | 2 | 40 |
| Masala Chai 250g + Masala Chips 90g | 2 | 40 |
How to solve Products Most Often Bought in the Same Basket
Market-basket pairs come from a self join on the bill: every two lines of the same bill form a candidate pair. Without a condition on the SKUs each pair would appear twice (A–B and B–A) and every product would pair with itself; a.sku < b.sku keeps exactly one orientation, the smaller SKU first, which is also the order the label needs.
Since a SKU appears at most once per bill (it is part of the key), each bill contributes at most one row per pair, so COUNT(*) after grouping by the two SKUs is the number of bills they share. HAVING COUNT(*) >= 2 keeps the recurring pairs. The label joins BasketProduct twice — once per side — and is built with CONCAT. Support is that count over the number of distinct bills, computed once in a scalar subquery and rounded to two places.
The alternative counts DISTINCT bill_id (safe even if a SKU could repeat on a bill), looks the names up with scalar subqueries, and cross-joins a one-row total. The self join is the cost: a bill with k lines produces k(k−1)/2 pairs, fine for baskets of normal size.
Reference solution (MySQL)
SELECT CONCAT(pa.product_name, ' + ', pb.product_name) AS pair,
COUNT(*) AS bills_together,
ROUND(100 * COUNT(*) / (SELECT COUNT(DISTINCT bill_id) FROM BillLine), 2) AS support_pct
FROM BillLine a
JOIN BillLine b ON b.bill_id = a.bill_id AND a.sku < b.sku
JOIN BasketProduct pa ON pa.sku = a.sku
JOIN BasketProduct pb ON pb.sku = b.sku
GROUP BY a.sku, b.sku, pa.product_name, pb.product_name
HAVING COUNT(*) >= 2
ORDER BY bills_together DESC, pairAnother way
WITH pairs AS (
SELECT a.sku AS sku_a, b.sku AS sku_b, COUNT(DISTINCT a.bill_id) AS n
FROM BillLine a CROSS JOIN BillLine b
WHERE a.bill_id = b.bill_id AND a.sku < b.sku
GROUP BY a.sku, b.sku
), total AS (SELECT COUNT(*) AS bills FROM (SELECT DISTINCT bill_id FROM BillLine) d)
SELECT CONCAT((SELECT product_name FROM BasketProduct WHERE sku = p.sku_a), ' + ', (SELECT product_name FROM BasketProduct WHERE sku = p.sku_b)) AS pair,
p.n AS bills_together, ROUND(100 * p.n / t.bills, 2) AS support_pct
FROM pairs p CROSS JOIN total t
WHERE p.n >= 2
ORDER BY bills_together DESC, pair← Daily Stock Carried Forward From the Last Count · Annual Enterprise Accounts Signed Up in 2024 →