SQL interview question: category pairs bought together
Problem
Shared data rulesFor each pair of categories, count the September 2026 orders that contain both. Also find their share of all September orders.
Formula
share = orders / all September orders
Example
orders| order_id | created_at | status |
|---|---|---|
| 101 | 2026-09-02 13:00 | delivered |
| 102 | 2026-09-05 18:20 | delivered |
| 103 | 2026-09-08 11:00 | delivered |
order_items| order_id | category | Row note |
|---|---|---|
| 101 | Produce | |
| 101 | Produce | ← second item, same category |
| 101 | Meat & seafood | |
| 102 | Meat & seafood | |
| 102 | Produce | |
| 103 | Pantry |
- Row 2: second item, same category
| category_a | category_b | orders | share |
|---|---|---|---|
| Meat & seafood | Produce | 2 | 0.6667 |
The pair “Meat & seafood” — “Produce” is in 2 orders out of 3: a share of 0.6667. Order 103 with a single category still counts in the denominator.
Notes
- Each pair is one row with category_a < category_b; skip pairs that never met in an order.
Solve it free
Your query runs on PostgreSQL in your browser. No sign-up needed.
Skills it tests
More interview questions
- SQL: Recursive employee hierarchyMedium · Hierarchies and recursion
- SQL: Customers who bought every categoryMedium · Relational division
- SQL: A/B test conversion rate by platformMedium · A/B tests
- SQL: 30-day customer lifetime valueHard · Product metrics
- SQL: Products that make 80% of revenueMedium · Running total