Products Most Often Bought in the Same Basket

Hard
397.

Products Most Often Bought in the Same Basket

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

ColumnType
skuvarchar
product_namevarchar

Primary key: sku.

Product names are unique.

Table: BillLine

ColumnType
bill_idint
skuvarchar
qtyint

Primary key (together): bill_id, sku.

One row per SKU on a bill; every sku is in BasketProduct.

Example

The rows must come back in the order shown.

BasketProduct

skuproduct_name
BEV-3001Masala Chai 250g
DAI-2002Paneer 200g
GRO-1002Toor Dal 1kg
SNK-6001Masala Chips 90g
GRO-1001Basmati Rice 5kg

BillLine

bill_idskuqty
1BEV-30011
1SNK-60012
1GRO-10021
2BEV-30012
2SNK-60011
3GRO-10011
3GRO-10022
4GRO-10021
4GRO-10011
5DAI-20023

Output

pairbills_togethersupport_pct
Basmati Rice 5kg + Toor Dal 1kg240
Masala Chai 250g + Masala Chips 90g240
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.

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

ColumnType
skuvarchar
product_namevarchar

Primary key: sku.

Product names are unique.

Table: BillLine

ColumnType
bill_idint
skuvarchar
qtyint

Primary key: bill_id, sku.

One row per SKU on a bill; every sku is in BasketProduct.

Examples

Example 1

BasketProduct

skuproduct_name
BEV-3001Masala Chai 250g
DAI-2002Paneer 200g
GRO-1002Toor Dal 1kg
SNK-6001Masala Chips 90g
GRO-1001Basmati Rice 5kg

BillLine

bill_idskuqty
1BEV-30011
1SNK-60012
1GRO-10021
2BEV-30012
2SNK-60011
3GRO-10011
3GRO-10022
4GRO-10021
4GRO-10011
5DAI-20023

Output

pairbills_togethersupport_pct
Basmati Rice 5kg + Toor Dal 1kg240
Masala Chai 250g + Masala Chips 90g240

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

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