Find all customers who, across completed orders in 2023, purchased items from at least 3 distinct product categories. Return customer_id, name, and the number of distinct categories they purchased from, ordered by category count descending.
Schema
customers
customer_idINTEGER
nameVARCHAR
emailVARCHAR
orders
order_idINTEGER
customer_idINTEGER
order_dateDATE
statusVARCHAR
status values: 'completed', 'cancelled'
order_items
item_idINTEGER
order_idINTEGER
product_idINTEGER
quantityINTEGER
priceDECIMAL
products
product_idINTEGER
nameVARCHAR
categoryVARCHAR
Target output
Target output
| customer_id | name | categories_purchased |
|---|
| 1 | Sarah Okonkwo | 4 |
| 2 | James Park | 3 |
| 3 | Maria Santos | 3 |
What this question tests
Tests a three-table walk through a normalised schema followed by COUNT(DISTINCT) and a threshold. The quiet check is whether you know a filter on an aggregate belongs in HAVING rather than WHERE, and whether your join path keeps completed orders only.
More SQL interview questions
