Coding questions · Amazon
Find Top Three Books by City in Recent Months
This question evaluates proficiency in time-windowed aggregation, group-level ranking and window-function usage on relational datasets, emphasizing handling of recent date ranges and top-N selection.
BOOK_SALES
+------+--------+------------+------------+
| city | asin | sale_date | units_sold |
+------+--------+------------+------------+
| NYC | B001 | 2025-04-01 | 120 |
| NYC | B002 | 2025-04-15 | 100 |
| NYC | B003 | 2025-04-20 | 90 |
+------+--------+------------+------------+
Scenario
You are analyzing monthly book sales stored in BOOK_SALES (city-level grain).
Question
Write a SQL query that returns, for each city, the top three books (asin) ranked by total units_sold over the most recent three-month window in the dataset. Output: city, asin, total_units_sold, rank.
Hints
Find the max(sale_date), filter rows within DATEADD('month',-3,max_date), aggregate, use DENSE_RANK() or ROW_NUMBER() partitioned by city over total_units_sold desc.
Top 3 Books per City in Most Recent Three-Month Window
Table BOOK_SALES
| column | type | constraints |
|---|---|---|
| city | VARCHAR(50) | NOT NULL |
| asin | VARCHAR(20) | NOT NULL |
| sale_date | DATE | NOT NULL |
| units_sold | INTEGER | NOT NULL |
Sample data:
| asin | city | sale_date | units_sold |
|---|---|---|---|
| B001 | NYC | 2025-04-01 | 120 |
| B002 | NYC | 2025-04-15 | 100 |
| B003 | NYC | 2025-04-20 | 90 |
Expected output
For each city, sum units_sold per asin within the most recent three-month window (relative to the maximum sale_date in BOOK_SALES), then rank books by total_units_sold in descending order. Return only ranks 1 through 3 per city.
| asin | city | rank | total_units_sold |
|---|---|---|---|
| B001 | NYC | 1 | 120 |
| B002 | NYC | 2 | 100 |
| B003 | NYC | 3 | 90 |
Hints
- First compute MAX(sale_date) from BOOK_SALES to identify the end of the window.
- Filter rows to those with sale_date in the last three months relative to that max date, aggregate SUM(units_sold) by city and asin, then use DENSE_RANK() or ROW_NUMBER() partitioned by city ordered by total_units_sold DESC, and keep only ranks 1 through 3.