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.
Quick Answer: 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.