Practice · hard · dataset: ecom
Customers who bought every category
A loyalty team wants customers who have explored the whole catalogue. Return every customer who has bought at least one product from each category that exists in the product table.
Return customer_id, country, and categories (the number of distinct categories that customer
bought). Sort by customer_id ascending.
Tables available
| Table | Columns |
|---|---|
customers |
customer_id, email, country, signup_date |
products |
product_id, name, category, unit_price |
orders |
order_id, customer_id, order_date, status, channel |
order_items |
order_id, product_id, quantity, unit_price |
The full set is defined by the categories currently in products, so the query must derive that
count rather than hardcode it.
⌘/Ctrl + Enter to grade
Write a query, then grade it.
Hints
Try it first. Reveal a hint only when you are stuck.