Back to questions
A Microsoft Azure Supercloud customer is a company which buys at least 1 product from each product category.
Write a query to report the company ID which is a Supercloud customer.
As of 5 Dec 2022, data in the and tables were updated.
|2||Analytics||Azure Stream Analytics|
|4||Containers||Azure Kubernetes Service|
|5||Containers||Azure Service Fabric|
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!
As usual, take a step back and identify the key points of solving the task. This is how we see it:
Join the two tables with either or .
Now, let's count all of the distinct product categories that a customer has purchased a product from.
We are almost there, hang on!
Let's wrap the previous step into a CTE, and select only the users who have products from all of the categories.
One way of doing this would be simply filtering for of 3 (as there are 3 product categories). However, let's be even smarter and make it dynamic by using a subquery referencing the products table instead.
I believe this is a pretty nice solution, don't you?