Count Customers Buying Both 'Kindle' and 'Alexa'
Company: Amazon
Role: Business Intelligence Engineer
Category: Data Manipulation (SQL/Python)
Difficulty: Medium
Interview Round: Onsite
ITEMS
+---------+-----------+
| ITEM_ID | ITEM_NAME |
+---------+-----------+
| 101 | Kindle |
| 202 | Alexa |
| 303 | Fire TV |
+---------+-----------+
ORDERS
+----------+-------------+
| ORDER_ID | CUSTOMER_ID |
+----------+-------------+
| 5001 | 1 |
| 5002 | 2 |
+----------+-------------+
ORDER_ITEM
+----------+---------+
| ORDER_ID | ITEM_ID |
+----------+---------+
| 5001 | 101 |
| 5001 | 202 |
| 5002 | 101 |
+----------+---------+
CUSTOMERS
+-------------+
| CUSTOMER_ID |
+-------------+
| 1 |
| 2 |
+-------------+
##### Scenario
E-commerce platform analyzing customer purchase patterns across items and orders tables.
##### Question
Write SQL to return the count of distinct CUSTOMER_IDs who purchased at least one 'Kindle' and at least one 'Alexa'.
##### Hints
Join orders, order_item, items; pivot/filter Kindle and Alexa; group by customer with HAVING.
Quick Answer: This question evaluates proficiency in relational data manipulation and set-based reasoning, assessing SQL competency such as joins, aggregation, and identifying distinct customers who purchased multiple specified products.