Katkat
Back

Count Customers Buying Both 'Kindle' and 'Alexa'

AmazonBusiness Intelligence Engineer · Onsite
MediumBusiness Intelligence EngineerOnsiteData Manipulation (SQL/Python)

Last updated

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.

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.