Subquery Problems

Problem 1: Contest Participation Rate

Problem Statement

An online contest platform tracks which users registered for which contests. The analytics team wants to know how popular each contest is, by finding what percentage of all users registered for it.

Business context A contest that 100% of users join is clearly popular. A contest that only 30% of users join may need better promotion. Showing the percentage (instead of a raw count) makes contests easy to compare.

Goal For each contest, calculate the percentage of total users who registered for it, rounded to 2 decimal places.

Input Two tables: Users (every user) and Register (which user registered for which contest).

Output contest_id and percentage, sorted by percentage in descending order. If two contests have the same percentage, sort by contest_id in ascending order.

Table Structure

Table: Users

Table: Register

Column Meaning

• user_id (Users) — a unique ID for each user, and the primary key
• user_name — the user's name
• contest_id — the ID of a contest
• user_id (Register) — the ID of a user who registered for that contest

Important Notes

• The primary key of Register is (contest_id, user_id). A user can register for a contest only once, but can register for many different contests.
• The percentage is based on the total number of users in the Users table, not on the number of rows in Register.
• A contest appears in the output only if it has at least one registration. A contest with zero registrations has no row in Register at all.
• The total number of users is a single value that is the same for every contest. That is a perfect job for a scalar subquery.
• Sorting has two levels: percentage descending first, then contest_id ascending for ties.
• Example 1 in some platforms shows 100.0. Rounding to 2 decimals gives 100.00, which is the same number.

Example

Input: Users

Input: Register

Expected Output

CREATE TABLE Users (user_id INTEGER, user_name TEXT);
INSERT INTO Users VALUES
  (6, 'Alice'),
  (2, 'Bob'),
  (7, 'Alex');
CREATE TABLE Register (contest_id INTEGER, user_id INTEGER);
INSERT INTO Register VALUES
  (215, 6),
  (209, 2),
  (208, 2),
  (210, 6),
  (208, 6),
  (209, 7),
  (209, 6),
  (215, 7),
  (208, 7),
  (210, 2),
  (207, 2),
  (210, 7);

Explanation

• Total users = 3
• Contest 208: 3 users, (3 / 3) × 100 = 100.00%
• Contest 209: 3 users, (3 / 3) × 100 = 100.00%
• Contest 210: 3 users, (3 / 3) × 100 = 100.00%
• Contest 215: 2 users, (2 / 3) × 100 = 66.67%
• Contest 207: 1 user, (1 / 3) × 100 = 33.33%

Extra Example (to see the tie-break rule)

Users: user_id 1, 2, 3, 4 (4 users in total).

Output:

Contests 100 and 103 both have 50.00%, so the smaller contest_id (100) comes first.

Intuition

To turn a count into a percentage, you need two numbers:

1. How many users registered for this contest (different for every contest).
2. How many users exist in total (the same for every contest).

The first number comes from grouping Register by contest. The second number does not belong to any contest group, it comes from a different table. A subquery is the clean way to bring in that single constant value.

Mental Model

Think of a school with 3 students and several clubs. A club's popularity is "how many of the 3 students joined," shown as a percentage of the whole school. The school's total (3) is written once on the notice board, and each club's count is divided by that same number.

 Total users (one number for all contests):  (SELECT COUNT(*) FROM Users) = 3

 Contest 208 -> 3 users -> 3 / 3 = 100.00%
 Contest 215 -> 2 users -> 2 / 3 =  66.67%
 Contest 207 -> 1 user  -> 1 / 3 =  33.33%

Thought Process

1. What is the final row? One row per contest, so GROUP BY contest_id on Register.
2. How do I count users per contest? COUNT(DISTINCT r.user_id).
3. Where does the total come from? The Users table. It is a different table and a single value, so use a scalar subquery: (SELECT COUNT(*) FROM Users).
4. How do I make a percentage? count * 100.0 / total. The 100.0 (with a decimal) keeps the division from being cut to a whole number in databases that use integer division.
5. How do I round? ROUND(..., 2).
6. How do I sort? ORDER BY percentage DESC, contest_id ASC.

Approach

Step 1: Count total users (SELECT COUNT(*) FROM Users) gives one number.

Step 2: Count registrations per contest GROUP BY r.contest_id and COUNT(DISTINCT r.user_id).

Step 3: Calculate the percentage COUNT(DISTINCT r.user_id) * 100.0 / total_users.

Step 4: Round to 2 decimal places Wrap it in ROUND(..., 2).

Step 5: Sort ORDER BY percentage DESC, r.contest_id ASC.

SQL Query

SELECT
  r.contest_id,
  -- users registered for this contest, as a percentage of ALL users
  ROUND(
    COUNT(DISTINCT r.user_id) * 100.0
    / (SELECT COUNT(*) FROM Users),   -- scalar subquery: total users, runs once
    2
  ) AS percentage
FROM Register r
GROUP BY r.contest_id                  -- one row per contest
ORDER BY percentage DESC,              -- most popular first
         r.contest_id ASC;             -- ties: smaller contest_id first

Query Explanation

• FROM Register r — the query starts from the registrations, because each contest's count comes from here.
• GROUP BY r.contest_id — puts all rows of the same contest into one group.
• COUNT(DISTINCT r.user_id) — counts the different users in each group. (Because the primary key already stops repeats inside one contest, DISTINCT is a safety net here, it does not change the result.)
• (SELECT COUNT(*) FROM Users) — a scalar subquery. It does not depend on the outer row, so it is calculated once and reused for every contest.
• * 100.0 / — converts the fraction into a percentage, using a decimal number to avoid whole-number division.
• ROUND(..., 2) — keeps 2 decimal places.
• ORDER BY percentage DESC, r.contest_id ASC — sorts by percentage first, then uses contest_id as the tie-breaker.

Dry Run

Original tables

Users: 3 rows (user_id 6, 2, 7). Register: 12 rows (shown in the example).

↓

Step 1: Run the subquery once

SELECT COUNT(*) FROM Users returns 3.

↓

Step 2: GROUP BY contest_id and count distinct users

↓

Step 3: Calculate the percentage (count × 100.0 / 3), then ROUND

↓

Step 4: ORDER BY percentage DESC, contest_id ASC

SQL Execution Flow

(SELECT COUNT(*) FROM Users)      runs once  ->  3
        |
FROM Register r
        |
GROUP BY r.contest_id
        |
COUNT(DISTINCT user_id) per group
        |
count * 100.0 / 3
        |
ROUND(..., 2)
        |
SELECT contest_id, percentage
        |
ORDER BY percentage DESC, contest_id ASC

Time Complexity

Time Complexity: O(u + r log r), where u is the number of users and r is the number of registration rows Counting Users is one pass (O(u)), and done once. Grouping Register usually needs sorting or hashing (O(r log r)). Sorting the final result is small, since it has only one row per contest.

Space Complexity: O(c), where c is the number of contests The database keeps one running count per contest.

Why: The subquery is not correlated, so it does not repeat for each contest. If it were correlated, the cost would multiply.

Common Mistakes to Avoid

Mistake 1: Dividing by the number of registration rows instead of the number of users

Why it happens: Both tables are named "users" in some way, and COUNT(*) is easy to type.

Wrong:

SELECT contest_id, ROUND(COUNT(*) * 100.0 / COUNT(*), 2) AS percentage
FROM Register
GROUP BY contest_id;

Both counts are the same inside a group, so every contest shows 100.00.

Correct: Divide by the total from the Users table: (SELECT COUNT(*) FROM Users).

Mistake 2: Hard-coding the total

Why it happens: The example has 3 users, so / 3 seems fine.

Wrong:

ROUND(COUNT(DISTINCT r.user_id) * 100.0 / 3, 2)

It breaks the moment a user is added or removed.

Correct: Use the subquery, so the total is always read from the table.

Mistake 3: Integer division

Why it happens: * 100 looks right.

Wrong (in databases that divide integers as integers):

COUNT(DISTINCT r.user_id) * 100 / (SELECT COUNT(*) FROM Users)

The result can be cut to a whole number (for example 66 instead of 66.67) before ROUND is applied.

Correct: Use 100.0, or cast one side to a decimal.

Mistake 4: Missing the second sort key

Why it happens: Sorting by percentage feels complete.

Wrong:

ORDER BY percentage DESC

Contests with the same percentage can come out in any order.

Correct: ORDER BY percentage DESC, r.contest_id ASC.

Mistake 5: Forgetting ROUND

Why it happens: The value looks fine in a quick test.

Wrong: returning 66.666667 where 66.67 is required.

Correct: ROUND(..., 2).

Mistake 6: Putting the subquery in the wrong place

Why it happens: Trying to use a column alias inside the same SELECT.

Wrong:

SELECT contest_id, COUNT(DISTINCT user_id) AS cnt, cnt * 100.0 / total ...

A column alias cannot be used inside the same SELECT list, and total does not exist.

Correct: Write the full expression, and place the subquery directly where the total is needed.

Interview Follow-up Questions

• "How would you rewrite this with a CROSS JOIN instead of a scalar subquery?" (A derived table of total users, cross joined to the grouped result.)
• "How would you include contests that nobody registered for?" (You would need a Contests table and a LEFT JOIN.)
• "Why is COUNT(DISTINCT user_id) safe but not required here?"
• "What if the Users table were empty? What would the division return?" (In MySQL, division by zero returns NULL.)
• "Is the subquery run once or once per contest? Why?"

Similar Problems

• Instant Food Delivery (an earlier percentage problem using COUNT and a total)
• Query Quality Analysis (percentage per group)
• Any "share of the total" report, such as market share per product or percentage of sales per region

Key SQL Concepts Learned

Pattern Recognition

When should you immediately think of this approach?

• Need "part out of the total" where the total comes from a different table or from the whole table → scalar subquery for the total
• The total is the same for every group → a non-correlated subquery that runs once is perfect
• Output must be a percentage with fixed decimals → ROUND(x * 100.0 / total, 2)
• Ties need a stable order → add a second ORDER BY column

Problem 2: High-Report Managers

Problem Statement

A company's HR system stores employees and who they report to. Management wants to find managers who have at least 5 employees reporting directly to them.

Business context Managers with many direct reports may be overloaded, and managers with very few may be under-used. Knowing who leads large teams helps HR plan hiring and reorganizations.

Goal Find all managers who have 5 or more direct reports.

Input An Employee table that stores each employee's manager.

Output The name of each such manager. The result can be in any order.

Table Structure

Table: Employee

Column Meaning

• id — a unique ID for each employee, and the primary key
• name — the employee's name
• department — the department the employee works in
• managerId — the id of the employee's direct manager; NULL if the employee has no manager

Important Notes

• Only direct reports count. If A manages B and B manages C, C is not a direct report of A.
• "At least 5" means >= 5, not > 5.
• A manager is also just a row in the same Employee table. Their own id appears in other rows' managerId.
• Employees with managerId = NULL (the top of the company) form their own group when you GROUP BY managerId. That group is harmless here, because it is only included if 5 or more employees have no manager, and even then a NULL in an IN list causes no trouble.
• Employees from different departments can report to the same manager (Lie in department B reports to Emily in department A), so department does not matter.

Example

Input: Employee

Expected Output

CREATE TABLE Employee (id INTEGER, name TEXT, department TEXT, managerId INTEGER);
INSERT INTO Employee VALUES
  (101, 'Emily', 'A', NULL),
  (102, 'Jak', 'A', 101),
  (103, 'Olive', 'A', 101),
  (104, 'Emy', 'A', 101),
  (105, 'Liam', 'A', 101),
  (106, 'Lie', 'B', 101);

Explanation

• Emily (id 101) has 5 direct reports (102, 103, 104, 105, 106), so she is included.
• Jak, Olive, Emy, Liam, and Lie have no direct reports at all, so they are excluded.

Extra Example (two qualifying managers)

Output:

Anna has 5 direct reports (2 to 6). Ben has 5 (7 to 11). Cara has only 1 (12). Nobody else has any.

Intuition

The table only stores "who reports to whom" one row at a time. To know how many people report to one manager, you count how many rows contain that manager's id in managerId. That is a grouping job. But the grouping gives you manager ids, and the question asks for manager names. So the plan is two steps:

1. Find the ids of managers who have at least 5 reports.
2. Look up the names for those ids.

Step 1 is the subquery. Step 2 is the outer query.

Mental Model

Imagine sorting employee cards into piles, one pile per manager id written in the managerId field. Count the cards in each pile. Keep only the piles with 5 or more cards. The label of each kept pile is a manager's id. Then look that id up in the employee list to read the manager's name.

 Inner query (group and count)               Outer query (look up names)

 managerId   count                            id in (101) ?
 NULL        1      -> not >= 5               101 Emily  -> yes -> keep
 101         5      -> >= 5  keep             102 Jak    -> no
                                              ...
        list of ids: (101)  ---------------->  result: Emily

Thought Process

1. How do I count direct reports? Group the rows by managerId and count them.
2. How do I keep only big teams? HAVING COUNT(*) >= 5. HAVING is used because the condition is about a group's count, not a single row.
3. What does this inner query return? A list of manager ids.
4. How do I turn ids into names? The outer query selects from Employee where id IN (that list).
5. Why IN? The inner result is a list of several possible ids, so IN is the natural fit. Also, IN is not bothered by a NULL group in the list.

Approach

Step 1: Group employees by managerId GROUP BY managerId in a subquery.

Step 2: Keep managers with 5 or more reports HAVING COUNT(*) >= 5.

Step 3: Return the manager ids from the subquery SELECT managerId.

Step 4: Get the names in the outer query SELECT name FROM Employee WHERE id IN (...).

SQL Query

SELECT name
FROM Employee
WHERE id IN (
  -- Inner query: ids of managers with 5 or more direct reports
  SELECT managerId
  FROM Employee
  GROUP BY managerId          -- one group per manager
  HAVING COUNT(*) >= 5        -- keep only big teams
);

Query Explanation

• Inner SELECT managerId FROM Employee GROUP BY managerId — makes one group for each distinct manager id (and one group for NULL).
• HAVING COUNT(*) >= 5 — filters the groups. HAVING works after grouping, so it can use the group's size.
• Outer WHERE id IN (...) — keeps only the employees whose own id appears in the list of qualifying manager ids.
• SELECT name — returns the names of those managers.

The inner query is non-correlated: it stands on its own and runs once.

Dry Run

Original table

↓

Step 1: Inner query, GROUP BY managerId

↓

Step 2: The inner query returns the list

(101)

↓

Step 3: Outer query, check every employee's id against the list

↓

Final output

SQL Execution Flow

Inner query:
   FROM Employee
      |
   GROUP BY managerId
      |
   HAVING COUNT(*) >= 5
      |
   SELECT managerId         --> list of manager ids (runs once)
      |
      v
Outer query:
   FROM Employee
      |
   WHERE id IN (list)
      |
   SELECT name

Time Complexity

Time Complexity: O(n log n), or O(n) with hash-based grouping The inner query scans the table once and groups it. The outer query checks each employee's id against a small list. Since id is the primary key, the lookups are fast.

Space Complexity: O(m), where m is the number of distinct managers The database keeps one counter for each manager id.

Why: Only one full pass for grouping is needed. The list of qualifying ids is normally short, so the outer check is cheap.

Common Mistakes to Avoid

Mistake 1: Using > 5 instead of >= 5

Why it happens: "At least 5" is easy to misread as "more than 5".

Wrong:

HAVING COUNT(*) > 5

Emily has exactly 5 reports and would be left out.

Correct: HAVING COUNT(*) >= 5.

Mistake 2: Using WHERE instead of HAVING for the count

Why it happens: WHERE is the usual filter.

Wrong:

SELECT managerId FROM Employee
WHERE COUNT(*) >= 5
GROUP BY managerId;

Aggregate functions cannot be used in WHERE. MySQL raises an error.

Correct: Use HAVING after GROUP BY.

Mistake 3: Returning two columns from the IN subquery

Why it happens: Wanting to see the count too.

Wrong:

WHERE id IN (SELECT managerId, COUNT(*) FROM Employee GROUP BY managerId HAVING COUNT(*) >= 5)

MySQL raises "Operand should contain 1 column(s)".

Correct: Select only managerId in the subquery.

Mistake 4: Returning the manager's name from the subquery

Why it happens: The final answer is a name, so it feels natural to select a name early.

Wrong idea: grouping by name inside the subquery does not describe a manager's reports at all, because managerId (not name) links reports to managers.

Correct: Group by managerId, then use the ids to find names in the outer query.

Mistake 5: Counting without GROUP BY

Why it happens: The HAVING clause looks enough.

Wrong:

SELECT managerId FROM Employee HAVING COUNT(*) >= 5;

Without GROUP BY, the whole table is one group, so you do not get a count per manager.

Correct: Add GROUP BY managerId.

Mistake 6: Counting indirect reports or counting the manager

Why it happens: Thinking of the whole team below a manager.

Why it matters: This problem counts only rows whose managerId equals the manager's id. A manager is never counted as their own report (Emily is in the NULL group, not in group 101).

Correct: Keep it to a direct managerId match, as the query does.

Interview Follow-up Questions

• "How would you solve this with a self JOIN instead of a subquery?" (Join Employee e to Employee m on e.managerId = m.id, then GROUP BY m.id, m.name HAVING COUNT(*) >= 5.)
• "Why should the self-join version group by m.id and not only m.name?" (Two managers can share a name.)
• "How would you also show how many reports each manager has?"
• "How would you find managers with exactly 5 direct reports?"
• "What happens to the NULL managerId group in the subquery? Would it ever cause a wrong answer with IN?"

Similar Problems

• Employees Earning More Than Their Manager (self join on managerId)
• The SELF JOIN scenario "Count Direct Reports per Manager" in the Day 28 joins notes
• Find Team Size for Each Employee (counting rows that share a value)

Key SQL Concepts Learned

Pattern Recognition

When should you immediately think of this approach?

• The answer is a name, but the counting is done on an id → subquery (or join) to go from ids to names
• Condition is about how many rows share a value (at least N) → GROUP BY + HAVING COUNT(*) >= N
• The inner query returns a list of ids → IN
• Table refers to itself (manager id points to employee id) → think subquery or self join

Problem 3: Customers Who Bought All Products

Problem Statement

A company wants to find customers who have purchased every product in its catalog. These are the most engaged customers, and the company can target them with special promotions and rewards.

Business context A customer who has tried every product is a strong candidate for a loyalty reward or an early-access offer.

Goal Return the customer_id of every customer who has bought all products listed in the Product table.

Input A Customer table (which customer bought which product) and a Product table (the full catalog).

Output customer_id, sorted in ascending order.

Table Structure

Table: Customer

Table: Product

Column Meaning

• customer_id — the ID of the customer
• product_key (Customer) — the ID of a product the customer purchased
• product_key (Product) — the ID of a product in the catalog

Important Notes

• The Customer table may contain duplicate rows. A customer can appear many times with the same product. Counting rows would be wrong, so we must count distinct products.
• "Bought all products" means the number of different products the customer bought equals the total number of products in the catalog.
• The grouped solution below assumes every product_key in Customer also exists in Product (like a foreign key). If that were not guaranteed, the NOT EXISTS version in the Interview section is safer.
• The result must be sorted by customer_id ascending.
• A customer who bought nothing has no row in Customer, so they never appear.

Example

Input: Customer

Input: Product

Expected Output

CREATE TABLE Customer (customer_id INTEGER, product_key INTEGER);
INSERT INTO Customer VALUES
  (1, 5),
  (2, 6),
  (3, 5),
  (3, 6),
  (1, 6);
CREATE TABLE Product (product_key INTEGER);
INSERT INTO Product VALUES
  (5),
  (6);

Explanation

• Customer 1 bought products 5 and 6, so they are included.
• Customer 2 bought only product 6, so they are excluded.
• Customer 3 bought products 5 and 6, so they are included.

Extra Example (why DISTINCT matters)

Customer:

Product: 5, 6 (two products).

Output:

Customer 4 has 2 rows, which equals the number of products, but both rows are product 5. They bought only one distinct product, so customer 4 is correctly excluded. COUNT(*) would have wrongly included customer 4.

Intuition

Being able to say "this customer bought everything" comes down to one comparison:

number of different products this customer bought = number of products that exist

The left side is calculated per customer (a grouping job). The right side is one number from the Product table (a scalar subquery). If the two numbers match, the customer has bought everything.

Mental Model

Picture a stamp collection card with one box for every product. Each purchase stamps a box. Buying the same product again stamps the same box, so it adds nothing new. A customer has "collected them all" when the number of stamped boxes equals the total number of boxes on the card.

 Catalog has 2 boxes:  [5] [6]

 Customer 1:  stamps 5, 6       -> 2 boxes stamped = 2  -> collected all
 Customer 2:  stamps 6          -> 1 box stamped   = 1  -> not all
 Customer 3:  stamps 5, 6       -> 2 boxes stamped = 2  -> collected all
 Customer 4:  stamps 5, 5       -> 1 box stamped   = 1  -> not all

Thought Process

1. One row per customer? Yes, so GROUP BY customer_id.
2. How many products has each customer bought? COUNT(DISTINCT product_key). DISTINCT handles duplicate rows.
3. How many products exist? SELECT COUNT(DISTINCT product_key) FROM Product. One number, so a scalar subquery.
4. Where does the comparison go? The count belongs to a group, so it goes in HAVING, not WHERE.
5. Sorting? ORDER BY customer_id.

Approach

Step 1: Count the total products (SELECT COUNT(DISTINCT product_key) FROM Product).

Step 2: Count distinct products per customer GROUP BY customer_id with COUNT(DISTINCT product_key).

Step 3: Compare In HAVING, keep customers where the two numbers are equal.

Step 4: Sort ORDER BY customer_id.

SQL Query

SELECT customer_id
FROM Customer
GROUP BY customer_id                               -- one group per customer
HAVING COUNT(DISTINCT product_key) =               -- different products this customer bought
       (SELECT COUNT(DISTINCT product_key)         -- total products in the catalog
        FROM Product)
ORDER BY customer_id;

Query Explanation

• GROUP BY customer_id — collects all purchase rows of the same customer together.
• COUNT(DISTINCT product_key) (outer) — counts the different products in each customer's group, ignoring repeated purchases.
• HAVING — filters groups after they are formed. It is the right place for a condition based on a count.
• (SELECT COUNT(DISTINCT product_key) FROM Product) — a scalar subquery that returns the size of the catalog. It does not depend on any customer, so it runs once.
• ORDER BY customer_id — returns the customers in ascending order.

Dry Run

Original tables

Customer:

Product: 5, 6.

↓

Step 1: Run the subquery once

Total distinct products in Product = 2.

↓

Step 2: GROUP BY customer_id and count distinct products

↓

Step 3: HAVING count = 2

↓

Step 4: ORDER BY customer_id

SQL Execution Flow

(SELECT COUNT(DISTINCT product_key) FROM Product)   runs once -> 2
        |
FROM Customer
        |
GROUP BY customer_id
        |
COUNT(DISTINCT product_key) per group
        |
HAVING count = 2
        |
SELECT customer_id
        |
ORDER BY customer_id

Time Complexity

Time Complexity: O(n log n), where n is the number of rows in Customer Grouping and counting distinct values per group typically needs sorting or hashing. The subquery is a small one-time pass over Product.

Space Complexity: O(n) in the worst case Counting distinct products for each customer needs to remember which products were seen for every customer.

Why: The expensive part is the DISTINCT count per customer. The subquery is cheap because it runs only once.

Common Mistakes to Avoid

Mistake 1: Using COUNT(*) or COUNT(product_key) without DISTINCT

Why it happens: Duplicate rows are easy to forget.

Wrong:

HAVING COUNT(product_key) = (SELECT COUNT(*) FROM Product)

In the extra example, customer 4 bought product 5 twice. The count is 2, which equals 2 products, so customer 4 is wrongly included.

Correct: COUNT(DISTINCT product_key).

Mistake 2: Hard-coding the number of products

Why it happens: The example has 2 products.

Wrong:

HAVING COUNT(DISTINCT product_key) = 2

It fails as soon as a new product is added.

Correct: Use the subquery, so the catalog size is always read from Product.

Mistake 3: Putting the count condition in WHERE

Why it happens: WHERE is the common filter.

Wrong:

SELECT customer_id FROM Customer
WHERE COUNT(DISTINCT product_key) = (SELECT COUNT(*) FROM Product)
GROUP BY customer_id;

Aggregates are not allowed in WHERE.

Correct: Use HAVING after GROUP BY.

Mistake 4: Forgetting GROUP BY

Why it happens: HAVING looks like a complete filter.

Wrong:

SELECT customer_id FROM Customer
HAVING COUNT(DISTINCT product_key) = (SELECT COUNT(*) FROM Product);

Without GROUP BY, the whole table becomes one group.

Correct: GROUP BY customer_id.

Mistake 5: Forgetting ORDER BY

Why it happens: The rows often come out sorted by accident on small data.

Correct: End with ORDER BY customer_id, because the problem asks for ascending order.

Mistake 6: Ignoring products that are not in the catalog

Why it happens: Assuming every purchased product is in Product.

Why it matters: If Customer had a product_key that is not in Product, the distinct count could reach the catalog size even though the customer missed a real catalog product.

Correct: Make sure product_key in Customer is a foreign key to Product, or use the NOT EXISTS version below, which only checks real catalog products.

Interview Follow-up Questions

• "Solve it without GROUP BY, using the double NOT EXISTS pattern." (See the query below.)
• "How would you find customers who bought at least one product but not all?"
• "How would you find customers who bought no product at all?" (They are not in Customer, so you need a Customers table.)
• "What changes if the Product table could have duplicate rows? Why does the subquery use DISTINCT?"
• "Which version is safer if Customer might contain product keys that are not in Product?"

Alternative solution: double NOT EXISTS

SELECT DISTINCT c.customer_id
FROM Customer c
WHERE NOT EXISTS (
  -- is there a product this customer did NOT buy?
  SELECT 1
  FROM Product p
  WHERE NOT EXISTS (
    SELECT 1
    FROM Customer c2
    WHERE c2.customer_id = c.customer_id
      AND c2.product_key = p.product_key
  )
)
ORDER BY c.customer_id;

How it reads: keep a customer if there is no product in the catalog that the customer has not bought. DISTINCT is needed because Customer has many rows per customer. This is the same pattern as "users who bought all products" in the concept notes.

Similar Problems

• Users who bought all products (Scenario 2 of NOT EXISTS in the concept notes)
• Students who completed every course, or drivers who visited every city
• Any "has every item in the set" question (often called relational division)

Key SQL Concepts Learned

Pattern Recognition

When should you immediately think of this approach?

• Need "has all" or "has every" items from a list → compare the customer's distinct count with the total count
• The table has repeated rows → always use COUNT(DISTINCT ...)
• The condition uses an aggregate → HAVING, never WHERE
• The total comes from another table → scalar subquery, never a hard-coded number