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.
Column Name | Type |
---|---|
customer_id | integer |
product_id | integer |
amount | integer |
customer_id | product_id | amount |
---|---|---|
1 | 1 | 1000 |
1 | 3 | 2000 |
1 | 5 | 1500 |
2 | 2 | 3000 |
2 | 6 | 2000 |
Column Name | Type |
---|---|
product_id | integer |
product_category | string |
product_name | string |
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 |
customer_id |
---|
1 |
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.
customer_id | unique_count |
---|---|
1 | 3 |
7 | 3 |
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?
Solution #2
PostgreSQL 14