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:
- Identify all unique product categories.
- Determine which customers have made purchases in each category.
- 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
Explanation:
CTE
CustomerCategories
:SELECT DISTINCT o.CustomerID, p.CategoryID
: Gets unique combinations of customers and categories they have purchased from.JOIN
clauses: JoinOrders
,OrderDetails
, andProducts
to link customers to product categories.
CTE
TotalCategories
:COUNT(DISTINCT CategoryID) AS TotalCategories
: Counts the total number of unique categories available.
CTE
CustomerCategoryCounts
:COUNT(DISTINCT cc.CategoryID) AS PurchasedCategories
: Counts how many distinct categories each customer has purchased from.GROUP BY cc.CustomerID
: Groups byCustomerID
to get counts per customer.
Final Query:
JOIN
withTotalCategories
: 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.
Post a Comment