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…