Analyze Top Book Sales and Unique Customer Purchases
Company: Amazon
Role: Business Intelligence Engineer
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
BOOK_TRANSACTION
+---------------+------------+-------------+------+----------+
| MARKETPLACE_ID| TXN_DAY | CUSTOMER_ID | ASIN | QUANTITY |
+---------------+------------+-------------+------+----------+
| 1 | 2025-06-01 | 10 | B001 | 2 |
| 1 | 2025-06-02 | 11 | B002 | 5 |
+---------------+------------+-------------+------+----------+
CATALOG
+---------------+------+--------------+
| MARKETPLACE_ID| ASIN | TITLE_NAME |
+---------------+------+--------------+
| 1 | B001 | Sample Book |
| 1 | B002 | Another Book |
+---------------+------+--------------+
MAGAZINE_TRANSACTION
+---------------+------------+-------------+------+----------+
| MARKETPLACE_ID| TXN_DAY | CUSTOMER_ID | ASIN | QUANTITY |
+---------------+------------+-------------+------+----------+
| 1 | 2025-06-01 | 12 | M001 | 1 |
+---------------+------------+-------------+------+----------+
##### Scenario
E-commerce marketplace wants sales insights from book and magazine transactions.
##### Question
Write an SQL query to return the top 100 books (by total QUANTITY) sold in the current calendar month across all marketplaces.
Write an SQL query to list CUSTOMER_IDs that purchased at least one book but zero magazines in the entire dataset.
##### Hints
Use DATE_TRUNC or YEAR/MONTH filters; GROUP BY ASIN with SUM; use LEFT JOIN or NOT EXISTS between book and magazine customer sets.
Quick Answer: This question evaluates proficiency in data manipulation and SQL querying, focusing on aggregation, date-based filtering, joins, and set-difference logic across transactional and catalog tables.