SQL Interview Questions Series #2

A daily series where I solve one SQL interview question and walk through my thought process.

🧩 The Problem Statement

We want to identify Supercloud customers on Microsoft Azure — defined as customers who have purchased at least one product from every product category available in the products table.

To determine this, we’ll need to analyze the customer_contracts table, which records product purchases by customer, and cross-reference it with the products table, which provides the category of each product. The goal is to return the customer_ids of those customers who have made purchases across all available product categories.

📊 Tables

customer_contracts table

customer_contracts input

products table

products input

💭 Step-by-Step Thought Process

1. Understand the goal: The objective is to identify customers who have made purchases from every distinct product category listed in the products table.
2. Understand the schema: There are two tables: product_id is the primary key (PK) in the products table and a foreign key (FK) in the customer_contracts table. So, we can join them on product_id.
3. Deciding on the strategy: This is where many people tend to overcomplicate things by jumping into CTEs or nested subqueries. But in this case, a simple JOIN, GROUP BY, and HAVING clause can get the job done efficiently.
4. Finding the number of distinct product categories: To make life easier, I decided to find out the distinct number of categories so that I can simply put the numerical value in theHAVING clause.

SELECT COUNT(distinct product_category)
FROM products;

-- the value was 3
-- or you can simply look at the categories without using the COUNT() operator.
-- in this case the output would be: Analytics, Containers and Compute

🧪 The Final SQL Query and Output:

SELECT c.customer_id
FROM customer_contracts c 
INNER JOIN products p 
ON c.product_id = p.product_id
GROUP BY customer_id
HAVING COUNT(DISTINCT product_category) =3;

Only the customer_id = 7 purchased at least one product from every category. If we look at all the purchases for this customer, we can verify that he/she indeed bought products from all different categories.

That’s it for today! The second medium-level SQL problem solved, explained, and documented. See you in the next one. (this one low-key was kinda easy) 🚀

🔗If you want to solve this problem, you can find it here.