23. Customers who bought every category
Asked in screens shaped like: Amazon, Instacart, Walmart
Marketing wants customers who have purchased at least one product in every product category. Count distinct categories from completed orders only. Return customer_id ascending.
Requirements
- Columns: customer_id
- Only completed orders
- COUNT(DISTINCT products.category) equals the number of categories in products
- Order by customer_id
Expected output
customer_id
Row order is graded, so ORDER BY matters here.
Tables this problem reads. The full warehouse is available in thesandbox.
orders
~200 rows
Order headers. One row per order, so amount is the order total — not a line total.
- order_idINTEGER— 1000–1199
- customer_idTEXT— C01–C12
- countryTEXT— US, DE, IN, UK, BR
- amountREAL
- statusTEXT— 'completed' or 'cancelled'
- ordered_atTEXT— YYYY-MM-DD HH:MM:SS
order_items
~250 rows
Order lines. Some orders carry two lines, which is what makes header joins fan out.
- order_idINTEGER— joins orders
- product_idTEXT— joins products
- qtyINTEGER
- unit_priceREAL
products
~5 rows
Product dimension across three categories.
- product_idTEXT— P01–P05
- nameTEXT
- categoryTEXT— saas, goods, services
- priceREAL
Pick a warehouse dialect above the editor. The engine is still SQLite; common syntax is rewritten before it runs.
SQLite cheat sheet
- Truncate to day
DATE(ordered_at) - Current date
DATE('now') - Conditional
CASE WHEN … THEN … ELSE … END - String aggregate
GROUP_CONCAT(col, ", ") - JSON field
json_extract(payload, '$.source') - Date difference
JULIANDAY(a) - JULIANDAY(b) - Filter a window
CTE, then WHERE rn = 1 - Median
ROW_NUMBER + COUNT(*) OVER ()
Revealed one at a time. The reference query stays in the Solution tab.
Hint 1
Join orders → order_items → products, then GROUP BY customer_id.
Hint 2
HAVING COUNT(DISTINCT category) = (SELECT COUNT(DISTINCT category) FROM products).
SELECT o.customer_id
FROM orders o
JOIN order_items i ON i.order_id = o.order_id
JOIN products p ON p.product_id = i.product_id
WHERE o.status = 'completed'
GROUP BY o.customer_id
HAVING COUNT(DISTINCT p.category) = (SELECT COUNT(DISTINCT category) FROM products)
ORDER BY o.customer_id;Why it works
This is a relational division. You do not list the categories — you count distinct categories per customer and compare to the catalog size. Forgetting the completed filter lets cancelled carts fake a full catalog.
Run your query to see the result set.
Submit to run the checks.
Why it works
This is a relational division. You do not list the categories — you count distinct categories per customer and compare to the catalog size. Forgetting the completed filter lets cancelled carts fake a full catalog.
Pro problem
Hard problems unlock with Pro
Easy pads stay free. Medium and hard SQL and Python problems — editor, tests, hints, and solutions — open after you upgrade to Pro or coaching.
See plansPractice free easy problems