Skip to the content

Often ordered together

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

productgoes_withbuyers
MouseMonitor2
KeyboardCable1
KeyboardMonitor1
KeyboardMouse1
LaptopHeadphones1
MonitorTablet1
MouseTablet1

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;
Solve this challenge

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

See all challenges →