Analyze Top Book Sales and Unique Customer Purchases

Q: How do I practice SQL interview questions?

PracHub provides an interactive SQL console where you can write and test queries against real database schemas. Get instant feedback and compare your solution with the expected output.

Q: What difficulty level is this coding question?

This is a Medium difficulty Data Manipulation (SQL/Python) question, commonly asked during Onsite rounds at Amazon.

Q: What role is this question designed for?

This question is commonly asked for Business Intelligence Engineer candidates at Amazon during technical interviews.

Question

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.

PracHub · Accepted 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.

Quick Overview

Quick Overview