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:

CategoryID	CategoryName
1	Electronics
2	Clothing
3	Books

Orders Table:

OrderID	CustomerID
1	1001
2	1002
3	1001

OrderDetails Table:

OrderID	ProductID	Quantity
1	101	1
2	102	2
3	103	3

Products Table:

ProductID	CategoryID
101	1
102	2
103	3

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.

https://mytecbooks.blogspot.com/2024/08/find-all-customers-who-made-purchases.html

Find All Customers Who Made Purchases in All Available Product Categories Query Using MS SQL Server programs interview questions and answers freshers