Katkat
Back

Find Top Three Books by City in Recent Months

AmazonBusiness Intelligence Engineer · Onsite
MediumBusiness Intelligence EngineerOnsiteData Manipulation (SQL/Python)

Last updated

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.