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.
Overview: 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.
Given the ITEMS, ORDERS, ORDER_ITEM, and CUSTOMERS tables, write a SQL query to return the count of distinct CUSTOMER_ID values for customers who purchased at least one 'Kindle' and at least one 'Alexa'.
Tables
ITEMS(ITEM_ID INTEGER, ITEM_NAME VARCHAR(100))
ORDERS(ORDER_ID INTEGER, CUSTOMER_ID INTEGER)
ORDER_ITEM(ORDER_ID INTEGER, ITEM_ID INTEGER)
CUSTOMERS(CUSTOMER_ID INTEGER)
Hints
- Join ORDERS to ORDER_ITEM to ITEMS.
- Filter for ITEM_NAME in ('Kindle', 'Alexa').