task Self joins · medium

Measure co-purchased product pairs

Find unordered pairs of distinct products appearing together in delivered orders. Return the smaller item id as item_a, the larger as item_b, and the number of shared orders.

what to return

Columns

item_aitem_bshared_orders in this order

Rows

14 rows · in the order asked

Watch for

Count an order once regardless of item quantities. The order_lines primary key is (order_id,item_id).

Only delivered orders qualify. Order by shared_orders descending, then item_a,item_b ascending.

Skill

Count distinct baskets supporting each unordered product combination. Refresher: Multi-grain joins without fan-out →

expected result

Your query should return exactly these columns and values. Run it and compare.

data you can query

Sample rows from each table. Each column shows its type underneath; → marks a link to another table.