Warehouse Bin Optimization

Efficiency in Amazon Fulfillment Centers depends on minimizing the travel distance for pickers. When items are frequently bought together but stored in different warehouse zones (e.g., Zone A and Zone C), it forces pickers to travel across the warehouse for a single order.

Write a query to identify pairs of products that have been ordered together in at least 3 separate orders but are currently stored in different warehouse zones.

Rules:

• A pair is defined as two distinct products appearing in the same order_id.
• To avoid double-counting pairs (e.g., Item 1 & Item 2 vs Item 2 & Item 1), always list the product with the lower product_id as product_a.
• Output columns: product_a, product_b, zone_a, zone_b, order_count.
• Only include pairs where zone_a != zone_b and order_count >= 3.
• Results must be sorted by order_count descending, then product_a ascending.