Back to questions
Supercloud Customer Microsoft SQL Interview Question
Supercloud Customer
Microsoft SQL Interview Question
A Microsoft Azure Supercloud customer is defined as a customer who has purchased at least one product from every product category listed in the table.
Write a query that identifies the customer IDs of these Supercloud customers.
Table:
| Column Name | Type |
|---|---|
| customer_id | integer |
| product_id | integer |
| amount | integer |
Example Input:
| customer_id | product_id | amount |
|---|---|---|
| 1 | 1 | 1000 |
| 1 | 3 | 2000 |
| 1 | 5 | 1500 |
| 2 | 2 | 3000 |
| 2 | 6 | 2000 |
Table:
| Column Name | Type |
|---|---|
| product_id | integer |
| product_category | string |
| product_name | string |
Example Input:
| product_id | product_category | product_name |
|---|---|---|
| 1 | Analytics | Azure Databricks |
| 2 | Analytics | Azure Stream Analytics |
| 4 | Containers | Azure Kubernetes Service |
| 5 | Containers | Azure Service Fabric |
| 6 | Compute | Virtual Machines |
| 7 | Compute | Azure Functions |
Example Output:
| customer_id |
|---|
| 1 |
Explanation:
Customer 1 bought from Analytics, Containers, and Compute categories of Azure, and thus is a Supercloud customer. Customer 2 isn't a Supercloud customer, since they don't buy any container services from Azure.
The dataset you are querying against may have different input & output - this is just an example!
Solution
Step 1: Count distinct product categories
First, join the and tables to count the distinct product categories each customer has purchased from.
This query is defined within a CTE named for subsequent use in the next step.
The CTE counts distinct product categories for each customer, ensuring only those who have engaged with every product category are included.
Step 2: Filter customers with every product category
Using the CTE, we filter the results to identify customer IDs who have purchased from every product category. To do this, use a subquery in clause to compare the product count from the CTE with the distinct count of product categories in the table.