A company's executives are interested in seeing who earns the most money in each of the company's departments. A high earner in a department is an employee who has a salary in the top three unique salaries for that department.
This advanced problem is a staple in Amazon interviews, testing your ability to perform ranking within groups—a pattern used extensively in their e-commerce analytics. Whether it's finding top products per category, best sellers per region, or highest-rated items per department, this type of query is fundamental to Amazon's data-driven decision making. The complexity lies in handling ties and ensuring you get exactly the top 3 unique salary levels.
Core concepts: correlated subqueries for ranking, COUNT(DISTINCT) for handling duplicates, window functions like DENSE_RANK() or ROW_NUMBER() as alternatives, JOIN operations between related tables, and understanding the difference between ranking methods (RANK vs DENSE_RANK vs ROW_NUMBER). The correlated subquery approach tests your ability to think in terms of row-by-row comparisons.
Amazon's analytics teams use similar queries daily to: generate top N product reports per category for homepage recommendations, identify best-performing sellers in each marketplace, calculate inventory priorities by ranking SKUs within warehouses, create executive dashboards showing top performers across organizational hierarchies, and feed machine learning models with ranked feature sets for personalization.
When tackling this Amazon problem, the key is to understand the grain of the result. Are you returning one row per user, or one row per category? Always start by identifying your unique join keys and consider if filtered aggregations (CASE WHEN) are more efficient than multiple subqueries.
Be careful with NULL values in your JOIN conditions or aggregate functions. In interview scenarios, datasets often include edge cases like zero-count categories or duplicate entries that can throw off a simple COUNT(*) if not handled with DISTINCT.
Share your approach, optimized queries, or ask questions. Learning from others is the fastest way to master SQL.