Drill 10 of 12 in Interview Classics
0

Interview Classics · Self-join · a.item < b.item · COUNT(DISTINCT)

10. Items bought together

Concept: Self-join · a.item < b.item · COUNT(DISTINCT)show

To find pairs inside a group, self-join the table on the group key. a.item < b.item keeps each unordered pair once (bagel + jam, never also jam + bagel) and drops an item paired with itself; <> would keep both orders. If an item can repeat inside a group, count distinct groups, not joined rows.

Example (different data):

SELECT a.actor AS actor_1, b.actor AS actor_2, COUNT(*) AS films
FROM cast_list a
JOIN cast_list b ON b.film_id = a.film_id AND a.actor < b.actor
GROUP BY a.actor, b.actor;

Your task

Classic interview question: the grocery app's merchandising team wants to know which items land in the same basket, to power 'often bought together' suggestions.

Return item_a, item_b (the two items of a pair, item_a alphabetically first) and baskets (number of different baskets containing both) for every pair found together in at least 2 baskets. Sort by baskets (highest first), then item_a, then item_b.

Starting Postgres in your browser…

Checking your access…