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.
Overview: 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.
Top Books in June 2025
Using `BOOK_TRANSACTION` and `CATALOG`, return the top 100 books by total quantity sold during June 2025, inclusive of 2025-06-01 and 2025-06-30. Include `ASIN`, `TITLE_NAME`, and `TOTAL_QUANTITY`. Join catalog titles by both marketplace and ASIN, aggregate across marketplaces by ASIN, and order by `TOTAL_QUANTITY` descending, then `ASIN` ascending.
Tables
BOOK_TRANSACTION(MARKETPLACE_ID INTEGER, TXN_DAY DATE, CUSTOMER_ID INTEGER, ASIN VARCHAR(20), QUANTITY INTEGER)
CATALOG(MARKETPLACE_ID INTEGER, ASIN VARCHAR(20), TITLE_NAME VARCHAR(255))
Hints
- Use a half-open June date range: `>= DATE '2025-06-01'` and `< DATE '2025-07-01'`.
- Join on both `MARKETPLACE_ID` and `ASIN`.
Customers Buying Books Only
List distinct CUSTOMER_IDs that purchased at least one book but zero magazines across the entire dataset, ordered by CUSTOMER_ID ascending.
Tables
BOOK_TRANSACTION(MARKETPLACE_ID INTEGER, TXN_DAY DATE, CUSTOMER_ID INTEGER, ASIN VARCHAR(20), QUANTITY INTEGER)
MAGAZINE_TRANSACTION(MARKETPLACE_ID INTEGER, TXN_DAY DATE, CUSTOMER_ID INTEGER, ASIN VARCHAR(20), QUANTITY INTEGER)
Hints
- Select distinct CUSTOMER_IDs from BOOK_TRANSACTION.
- Use NOT EXISTS or a left anti-join to exclude any customers that appear in MAGAZINE_TRANSACTION.