Advanced
Case 45 of 50
Case 045: The Strongest Pairing
order_itemsproducts
The self-join from the "Bought Together" case found which products get bought together — but a raw count alone doesn't say which pairings actually matter.
The manager wants that turned into a rate: "A pair that shows up twice out of two orders is stronger than one that shows up twice out of two hundred. Could you rank them so that actually shows?"
The data Alex is looking at
order_items
| order_id | product_id | quantity |
|---|---|---|
| 101 | 1 | 2 |
| 101 | 2 | 1 |
| 102 | 3 | 1 |
| 103 | 2 | 3 |
| 104 | 5 | 1 |
| … 29 more rows | ||
products
| id | name | category | price |
|---|---|---|---|
| 1 | Keyboard | Accessories | 2500 |
| 2 | Mouse | Accessories | 1200 |
| 3 | Headphones | Audio | 3500 |
| 4 | Monitor | Displays | 12000 |
| 5 | Laptop | Computers | 60000 |
| … 5 more rows | |||
TASK
Help Alex answer the manager:
“For every product, find its strongest co-purchased partner and the percentage of that product's orders that included the partner.”
Write a query above and hit Run to see what comes back.
No hints requested yet — click "Get a hint" when you're stuck.
Alex's final query — this is for reference. Copy it into your own thinking, not into the editor above.
WITH product_orders AS (
SELECT product_id, COUNT(DISTINCT order_id) AS order_count
FROM order_items
GROUP BY product_id
),
pairs AS (
SELECT
a.product_id AS product_a,
b.product_id AS product_b,
COUNT(DISTINCT a.order_id) AS pair_count
FROM order_items a
JOIN order_items b ON a.order_id = b.order_id AND a.product_id <> b.product_id
GROUP BY a.product_id, b.product_id
),
rates AS (
SELECT
pairs.product_a,
pairs.product_b,
pairs.pair_count,
ROUND(pairs.pair_count * 100.0 / product_orders.order_count, 1) AS attach_rate_pct,
ROW_NUMBER() OVER (PARTITION BY pairs.product_a ORDER BY pairs.pair_count DESC) AS rn
FROM pairs
JOIN product_orders ON product_orders.product_id = pairs.product_a
)
SELECT
p1.name AS product,
p2.name AS best_paired_with,
rates.attach_rate_pct
FROM rates
JOIN products p1 ON p1.id = rates.product_a
JOIN products p2 ON p2.id = rates.product_b
WHERE rates.rn = 1
ORDER BY rates.attach_rate_pct DESC;
What that query returns
Case Debrief
Self-join + ratio (multi-step CTE)
- Chaining several CTEs lets you build a complex answer as a sequence of simple, individually-readable steps — this is how real analytical queries are actually written.