Deviopath › PostgreSQL

Frequently Bought Together

Hard · Joins

Market basket analysis looks for products that end up in the same order. Join order_items to itself on order_id to get every pair of products in an order, and keep a.product_id < b.product_id so a pair is not counted twice (or paired with itself).

Count, for each pair of different products, in how many distinct orders they were bought together. Watch out: the same product can appear on two lines of one order, and that order still counts once for the pair. Only return pairs bought together in at least 2 orders.

Return product_a (name of the product with the smaller id), product_b (name of the product with the larger id) and orders_together, sorted by orders_together descending, then product_a, then product_b.

Next: All Subcategories