Objective

Write a SQL query to identify the customer IDs of those who have purchased at least one product from every product category.

Table Schema:

• customer_purchases

• product_catalog

Task Requirements:

• Join purchase data with the product catalog to determine which categories each customer has bought from
• Identify customers who have covered every distinct category in the catalog
• A customer may have multiple purchases in the same category - this still counts as one category
• Sort the result by customer_id in ascending order
  
  

Output columns: customer_id

SELECT cp.customer_id
FROM customer_purchases cp
JOIN product_catalog pc
    ON cp.product_id = pc.product_id
GROUP BY cp.customer_id
HAVING COUNT(DISTINCT pc.category) = (
    SELECT COUNT(DISTINCT category)
    FROM product_catalog
)
ORDER BY cp.customer_id;

Explanation

Step 1: Joining Purchases with the Product Catalog

FROM customer_purchases cp
JOIN product_catalog pc
    ON cp.product_id = pc.product_id

An INNER JOIN links each purchase to its product details. This lets us access the category column from product_catalog for every purchase a customer has made. Purchases with invalid product_ids are naturally excluded.

Step 2: Grouping by Customer

GROUP BY cp.customer_id

GROUP BY collapses all purchases for each customer into a single group. This sets up the aggregation needed to count how many distinct categories each customer has bought from.

Step 3: Counting Distinct Categories per Customer

HAVING COUNT(DISTINCT pc.category)

COUNT(DISTINCT pc.category) counts the number of unique categories each customer has purchased from. The DISTINCT keyword is essential here. Without it, multiple purchases in the same category would inflate the count.

Step 4: Getting the Total Category Count with a Subquery

(SELECT COUNT(DISTINCT category) FROM product_catalog)

This subquery calculates the total number of distinct categories that exist in the entire catalog. It runs once and provides the benchmark that each customer's category count is compared against.

Step 5: Filtering with HAVING

HAVING COUNT(DISTINCT pc.category) = (
    SELECT COUNT(DISTINCT category)
    FROM product_catalog
)

The HAVING clause filters grouped results. Only customers whose distinct category count equals the total number of categories pass through. This is the SQL equivalent of "purchased from every category." We use HAVING instead of WHERE because the condition depends on an aggregate function (COUNT).

Step 6: Sorting the Result

ORDER BY cp.customer_id

The output is sorted by customer_id in ascending order for consistent, readable results.