← Amazon Interview Insights

Amazon·Software Engineer·Technical Phone Screen·Intermediate

Intermediate
Jul 2026

Summary

Amazon BI Engineer interview with a SQL-heavy technical screen. One question, pretty focused, all about window functions and ranking logic over a filtered date range.

Questions Asked (1)

Q1

Given a table of city-level book sales, write a SQL query that returns the top 3 books per city by total units sold, but only looking at the most recent 3-month window in the data. Output should include city, book identifier, total units sold, and rank.

Data ModelingProduct Analytics & Metrics
Author's notes

The date filtering part is what tripped me up a bit.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

First, determine the most recent 3-month window by finding the maximum date in the sales table and filtering to the 3 months prior. Then aggregate total units sold per city and book within that window, and use a window function like ROW_NUMBER() or RANK() partitioned by city and ordered by total units descending to assign ranks. Finally, filter to ranks 1-3 and output the required columns.

Pro tip: Clarify whether 'most recent 3-month window' means the last 3 calendar months from the max date or the last 3 full months; also mention that ties in sales should be handled consistently (e.g., using RANK() to include ties or ROW_NUMBER() to force unique ranks).

1. Identify the most recent 3-month window

Find the maximum date in the sales data and define the window as the 3 months leading up to that date (e.g., using DATE_SUB or INTERVAL). Filter the sales records to only include transactions within that window.

2. Aggregate total units sold per city and book

Group the filtered data by city and book identifier, summing the units sold to get total sales for each combination.

3. Rank books within each city

Use a window function such as ROW_NUMBER() or RANK() over a partition by city, ordered by total units sold descending, to assign a rank to each book.

4. Filter to top 3 books per city

Apply a filter to keep only rows where the rank is less than or equal to 3.

5. Select and format the output

Return the city, book identifier, total units sold, and rank columns as specified, ensuring the final result is ordered appropriately (e.g., by city and rank).

Key Points to Mention

  • Use of window functions (ROW_NUMBER, RANK, DENSE_RANK) for per-group ranking
  • Date filtering logic to define the most recent 3-month window (e.g., based on MAX(date) or CURRENT_DATE)
  • Aggregation with SUM and GROUP BY before ranking
  • Handling ties in sales (e.g., RANK vs ROW_NUMBER) and its impact on the top 3
  • Performance considerations: indexing on date and city, and avoiding unnecessary subqueries
  • Edge cases: cities with fewer than 3 books, missing dates, or null values

AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.