Identify Customers Purchasing Every Product in Catalog
Company: Amazon
Role: Business Intelligence Engineer
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
X_PURCHASES
+-------------+---------+
| customer_id | pro_key |
+-------------+---------+
| 1 | A |
| 1 | B |
| 2 | A |
+-------------+---------+
PRODUCT_CATALOG_Y
+---------+
| pro_key |
+---------+
| A |
| B |
+---------+
##### Scenario
E-commerce team wants to identify customers who have purchased every product currently in the catalog.
##### Question
Using SQL (or Python), return the customer_id values who have at least one purchase in X_PURCHASES for every pro_key that appears in PRODUCT_CATALOG_Y.
##### Hints
Group by customer and compare counts, or use NOT EXISTS to filter missing products.
Overview: The question evaluates a candidate's ability to perform data manipulation in SQL or Python, focusing on aggregation, grouping, and set-coverage concepts (relational division) to determine which customers have purchased every product in a catalog.
You are given two tables: X_PURCHASES, which records product purchases by customers, and PRODUCT_CATALOG_Y, which lists all products currently in the catalog. Using SQL, return the customer_id values for customers who have at least one purchase in X_PURCHASES for every pro_key that appears in PRODUCT_CATALOG_Y.
Tables
X_PURCHASES(customer_id INTEGER, pro_key VARCHAR(10))
PRODUCT_CATALOG_Y(pro_key VARCHAR(10))
Hints
- Join purchases to the product catalog and group by customer_id.
- In the HAVING clause, compare the count of distinct purchased products to the total count of products in PRODUCT_CATALOG_Y.