Count Customers Buying Both 'Kindle' and 'Alexa'

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.

Count customers who purchased both Kindle and Alexa

Table ITEMS

column	type	constraints
ITEM_ID	INTEGER	PRIMARY KEY, NOT NULL
ITEM_NAME	VARCHAR(100)	NOT NULL

Sample data:

ITEM_ID	ITEM_NAME
101	Kindle
202	Alexa
303	Fire TV

Table ORDERS

column	type	constraints
ORDER_ID	INTEGER	PRIMARY KEY, NOT NULL
CUSTOMER_ID	INTEGER	NOT NULL, FOREIGN KEY -> CUSTOMERS.CUSTOMER_ID

Sample data:

CUSTOMER_ID	ORDER_ID
1	5001
2	5002

Table ORDER_ITEM

column	type	constraints
ORDER_ID	INTEGER	NOT NULL, PRIMARY KEY (composite), FOREIGN KEY -> ORDERS.ORDER_ID
ITEM_ID	INTEGER	NOT NULL, PRIMARY KEY (composite), FOREIGN KEY -> ITEMS.ITEM_ID

Sample data:

ITEM_ID	ORDER_ID
101	5001
202	5001
101	5002

Table CUSTOMERS

column	type	constraints
CUSTOMER_ID	INTEGER	PRIMARY KEY, NOT NULL

Sample data:

CUSTOMER_ID
1
2

Expected output

Single-row result giving the number of distinct customers who have purchased both 'Kindle' and 'Alexa'. Based on the sample data, only CUSTOMER_ID = 1 qualifies.

customer_count
1

Hints

1. Join ORDERS to ORDER_ITEM to ITEMS.
2. Filter for ITEM_NAME in ('Kindle', 'Alexa').
3. Group by CUSTOMER_ID and use HAVING to require both items.
4. Count the qualifying customers.