Analyze Top Book Sales and Unique Customer Purchases

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.

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.

Table BOOK_TRANSACTION

column	type	constraints
MARKETPLACE_ID	INTEGER	NOT NULL
TXN_DAY	DATE	NOT NULL
CUSTOMER_ID	INTEGER	NOT NULL
ASIN	VARCHAR(20)	NOT NULL
QUANTITY	INTEGER	NOT NULL

Sample data:

ASIN	CUSTOMER_ID	MARKETPLACE_ID	QUANTITY	TXN_DAY
B001	10	1	2	2025-06-01
B002	11	1	5	2025-06-02

Table CATALOG

column	type	constraints
MARKETPLACE_ID	INTEGER	NOT NULL
ASIN	VARCHAR(20)	NOT NULL
TITLE_NAME	VARCHAR(255)	NOT NULL

Sample data:

ASIN	MARKETPLACE_ID	TITLE_NAME
B001	1	Sample Book
B002	1	Another Book

Expected output

Top books by total quantity sold in June 2025 across all marketplaces. Based on the sample data, B002 has a higher total quantity than B001.

ASIN	TITLE_NAME	TOTAL_QUANTITY
B002	Another Book	5
B001	Sample Book	2

Hints

1. Use a half-open June date range: >= DATE '2025-06-01' and < DATE '2025-07-01'.
2. Join on both MARKETPLACE_ID and ASIN.
3. Group by ASIN and order by the summed quantity descending.

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.

Table BOOK_TRANSACTION

column	type	constraints
MARKETPLACE_ID	INTEGER	NOT NULL
TXN_DAY	DATE	NOT NULL
CUSTOMER_ID	INTEGER	NOT NULL
ASIN	VARCHAR(20)	NOT NULL
QUANTITY	INTEGER	NOT NULL

Sample data:

ASIN	CUSTOMER_ID	MARKETPLACE_ID	QUANTITY	TXN_DAY
B001	10	1	2	2025-06-01
B002	11	1	5	2025-06-02

Table MAGAZINE_TRANSACTION

column	type	constraints
MARKETPLACE_ID	INTEGER	NOT NULL
TXN_DAY	DATE	NOT NULL
CUSTOMER_ID	INTEGER	NOT NULL
ASIN	VARCHAR(20)	NOT NULL
QUANTITY	INTEGER	NOT NULL

Sample data:

ASIN	CUSTOMER_ID	MARKETPLACE_ID	QUANTITY	TXN_DAY
M001	12	1	1	2025-06-01

Expected output

Distinct customers who have at least one book transaction and no magazine transactions in the dataset.

CUSTOMER_ID
10
11

Hints

1. Select distinct CUSTOMER_IDs from BOOK_TRANSACTION.
2. Use NOT EXISTS or a left anti-join to exclude any customers that appear in MAGAZINE_TRANSACTION.