Supercloud Customer
ASKED IN INTERVIEWMicrosoft
Problem Statement
Microsoft Azure wants to identify its Supercloud customers. A Supercloud customer is defined as a customer who has purchased at least one product from every single product category offered.
Write an SQL query to find the customer_id of these Supercloud customers.
Rules:
- You must account for all categories present in the categories table.
- A customer must have at least one purchase record associated with every category_id.
- Output a single column: customer_id.
- Results must be sorted by customer_id in ascending order.
Tests your understanding of
Relational Division, Aggregation, JOIN and Filtering
Input Tables
categories
| category_id(INTEGER) | category_name(VARCHAR) |
|---|---|
| 1 | Compute |
| 2 | Storage |
| 3 | Analytics |
products
| product_id(INTEGER) | product_name(VARCHAR) | category_id(INTEGER) |
|---|---|---|
| 101 | Azure VM | 1 |
| 102 | Blob Storage | 2 |
| 103 | Data Factory | 3 |
| 104 | Functions | 1 |
customer_purchases
| customer_id(INTEGER) | product_id(INTEGER) |
|---|---|
| 1 | 101 |
| 1 | 102 |
| 1 | 103 |
| 2 | 101 |
| 2 | 102 |
| 3 | 101 |
| 3 | 102 |
| 3 | 103 |
| 3 | 104 |
| 4 | 103 |
Expected Output
| customer_id(INTEGER) |
|---|
| 1 |
| 3 |
Tags
MediumASKED IN INTERVIEWRelational DivisionAggregationJOINFiltering
20-25 min
46%