Find All Customers Who Made Purchases in All Available Product Categories

Find All Customers Who Made Purchases in All Available Product Categories Query Using MS SQL Server?


To find all customers who have made purchases in all available product categories, you'll need to follow these steps:

  1. Identify all unique product categories.
  2. Determine which customers have made purchases in each category.
  3. Filter customers who have made purchases in every category.

Assuming you have the following tables:

  • Orders:

    • OrderID
    • CustomerID
  • OrderDetails:

    • OrderID
    • ProductID
    • Quantity
  • Products:

    • ProductID
    • CategoryID
  • Categories:

    • CategoryID
    • CategoryName

Here’s how you can write a SQL query to find all customers who made purchases in all available product categories:

SQL Query

WITH CustomerCategories AS (
    SELECT DISTINCT
        o.CustomerID,
        p.CategoryID
    FROM Orders o
    JOIN OrderDetails od ON o.OrderID = od.OrderID
    JOIN Products p ON od.ProductID = p.ProductID
),
TotalCategories AS (
    SELECT COUNT(DISTINCT CategoryID) AS TotalCategories
    FROM Categories
),
CustomerCategoryCounts AS (
    SELECT
        cc.CustomerID,
        COUNT(DISTINCT cc.CategoryID) AS PurchasedCategories
    FROM CustomerCategories cc
    GROUP BY cc.CustomerID
)
SELECT
    c.CustomerID
FROM CustomerCategoryCounts c
JOIN TotalCategories t ON c.PurchasedCategories = t.TotalCategories;

Explanation:

  1. CTE CustomerCategories:

    • SELECT DISTINCT o.CustomerID, p.CategoryID: Gets unique combinations of customers and categories they have purchased from.
    • JOIN clauses: Join Orders, OrderDetails, and Products to link customers to product categories.
  2. CTE TotalCategories:

    • COUNT(DISTINCT CategoryID) AS TotalCategories: Counts the total number of unique categories available.
  3. CTE CustomerCategoryCounts:

    • COUNT(DISTINCT cc.CategoryID) AS PurchasedCategories: Counts how many distinct categories each customer has purchased from.
    • GROUP BY cc.CustomerID: Groups by CustomerID to get counts per customer.
  4. Final Query:

    • JOIN with TotalCategories: Filters customers to include only those whose count of purchased categories matches the total number of available categories.

Example Data and Results

Assuming the following data:

Categories Table:

CategoryIDCategoryName
1Electronics
2Clothing
3Books

Orders Table:

OrderIDCustomerID
11001
21002
31001

OrderDetails Table:

OrderIDProductIDQuantity
11011
21022
31033

Products Table:

ProductIDCategoryID
1011
1022
1033

In this case:

  • Customer 1001 has purchased products in all three categories.
  • Customer 1002 has purchased products in only one category.

The result of the query would be:

CustomerID
1001

This result shows customers who have made purchases in every product category available.

You can run this query in SQL Server Management Studio or any SQL query tool connected to your SQL Server to find customers who have purchased from all product categories.


Post a Comment

Previous Post Next Post