Often ordered together
- O(n log n) · Hard
- Full plan
- SQL
- joins
- grouping
Problem
"Customers who ordered this also ordered…" The owner wants that line on every product page, and that takes knowing which products end up in the same hands. Two products make a pair when the same customer has ordered both, even on different days.
Show the pair's two names and then how many customers have ordered both. Each pair shows up once, with the product with the lower id first: Mouse and Monitor, not the other way around. A customer counts once per pair, however many times they've ordered each thing. The order dated the 20th is in: what's wrong there is the date, not what was ordered. Sort from the pair with the most customers to the one with the fewest; ties go by the first name and then by the second.
What it has to return
These rows, in this order
| product | goes_with | buyers |
|---|---|---|
| Mouse | Monitor | 2 |
| Keyboard | Cable | 1 |
| Keyboard | Monitor | 1 |
| Keyboard | Mouse | 1 |
| Laptop | Headphones | 1 |
| Monitor | Tablet | 1 |
| Mouse | Tablet | 1 |
This is what comes out with the data in the practice database this challenge uses. The column names don’t count, so name yours however you like: what counts is how many there are and the order they come in. Besides this one, the challenge has hidden tests with other data that are revealed when you submit your solution.
You start with this
SQL
SELECT o.customer_id,
o.product_id
FROM orders o
ORDER BY o.customer_id;It opens in your browser, with the editor and the tests. This challenge is part of the full plan; the O(1) and O(log n) ones are free.
More O(n log n) challenges
- One student, one voteSQL · subqueries · grouping
- Report cards read acrossSQL · joins · grouping · conditionals
- Spent nothing, still on the listSQL · joins · nulls · grouping
- The easy gradersSQL · subqueries · sets · joins
- The hallway listSQL · joins · grouping · sorting
- Their latest, front and centerSQL · subqueries · joins