Find Top Three Books by City in Recent Months
Company: Amazon
Role: Business Intelligence Engineer
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Onsite
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.
Overview: 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.
You are analyzing monthly book sales stored in BOOK_SALES at a city-level grain. 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 (relative to the maximum sale_date present in BOOK_SALES). Output columns: city, asin, total_units_sold, rank.
Tables
BOOK_SALES(city VARCHAR(50), asin VARCHAR(20), sale_date DATE, units_sold INTEGER)
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.