Find Top Three Books by City in Recent Months

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_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.

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

Quick Overview

Find Top Three Books by City in Recent Months