Market basket: products bought together
SQL
Hard
2 views
Problem Description
From OrderItems(order_id, product_id), count how often two products are bought in the same order. Return top 10 pairs by count.
Sample Test Case
Input:
OrderItems(order_id, product_id)
Constraints
Think: self join on order_id and keep product_a < product_b
Official Solution
SELECT a.product_id AS product_a, b.product_id AS product_b, COUNT(DISTINCT a.order_id) AS orders_together FROM OrderItems a JOIN OrderItems b ON a.order_id = b.order_id AND a.product_id < b.product_id GROUP BY a.product_id, b.product_id ORDER BY orders_together DESC LIMIT 10;
Solutions (0)
No solutions submitted yet. Be the first!
No comments yet. Start the discussion!