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.
SELECT
d.name AS Department,
e.name AS Employee,
e.salary AS Salary
FROM employee e
JOIN department d ON e.departmentId = d.id
WHERE (
SELECT COUNT(DISTINCT e2.salary)
FROM employee e2
WHERE e2.salary > e.salary
AND e2.departmentId = e.departmentId
) < 3;For each employee, the correlated subquery counts how many distinct salary values within the same department exceed their salary. If fewer than 3 higher distinct salaries exist, the employee is in the top 3. The DISTINCT is critical — without it, five employees at the same top salary would each 'count' against the limit, incorrectly excluding peers. This approach is conceptually direct and works on all SQL dialects but can be slow on large tables because the subquery executes once per employee row. It is often the answer interviewers expect for its clarity.
WITH ranked AS (
SELECT
e.name AS Employee,
e.salary AS Salary,
e.departmentId,
DENSE_RANK() OVER (
PARTITION BY e.departmentId
ORDER BY e.salary DESC
) AS rnk
FROM employee e
)
SELECT
d.name AS Department,
r.Employee,
r.Salary
FROM ranked r
JOIN department d ON r.departmentId = d.id
WHERE r.rnk <= 3;DENSE_RANK() partitioned by department and ordered by salary descending assigns rank 1 to the highest salary, rank 2 to the next distinct salary, and so on — handling ties correctly by giving the same rank to equal salaries. Filtering WHERE rnk <= 3 then selects all employees in the top 3 salary tiers. This is a single-pass window operation with no per-row correlated subquery, making it significantly faster on large tables. It is also more readable and is the idiomatic modern SQL solution preferred in production. Supported in MySQL 8+, PostgreSQL, SQL Server, and BigQuery.
The correlated subquery approach has O(n²) complexity in the worst case: for each of the n employees, it scans up to n employees in the same department to count higher salaries. With a composite index on employee(departmentId, salary DESC), each inner scan becomes an index range scan rather than a full table scan, reducing it to O(n log n) effectively. The DENSE_RANK window function approach performs a single sort pass per partition — O(n log n) overall — and the optimizer may use an existing index on (departmentId, salary) to avoid sorting entirely, making it the clear winner at scale. For tables with millions of employees across thousands of departments, the window function approach is often 10–100x faster. Always check EXPLAIN output to confirm PARTITION BY is using the index.
The most critical mistake is omitting DISTINCT in the correlated subquery — COUNT(e2.salary) instead of COUNT(DISTINCT e2.salary) — which counts employees rather than unique salary levels, incorrectly excluding employees who share a salary with someone ranked higher. Another common error is using RANK() instead of DENSE_RANK(): RANK() skips numbers after ties (1,1,3), so filtering rnk <= 3 might miss the actual third distinct salary tier. Candidates also sometimes join the department table incorrectly or forget it entirely, returning departmentId integers instead of department names. Finally, using TOP 3 with ORDER BY in some dialects returns at most 3 rows globally, not per department.
ROW_NUMBER assigns unique sequential integers regardless of ties — two employees with the same salary get different numbers, so some top-tier employees would be arbitrarily excluded. RANK skips ranks after ties (1,1,3), meaning if two employees share rank 1, the next rank is 3 — filtering rnk <= 3 would include all of rank 3 but skip rank 2 implicitly, which is still correct here but confusing. DENSE_RANK never skips (1,1,2), cleanly identifying the three distinct salary tiers regardless of how many employees share each level.
With DENSE_RANK, simply change the filter to WHERE rnk = 3 instead of rnk <= 3. This returns only employees whose salary matches the third distinct salary value in their department. If a department has fewer than 3 distinct salary levels, no rows are returned for that department — which is correct behavior. With the correlated subquery approach, change < 3 to = 2, selecting employees where exactly 2 distinct salaries in their department exceed theirs.
Both solutions handle this gracefully: the correlated subquery returns all employees when fewer than 3 higher salaries exist (since the count is always < 3), and the DENSE_RANK approach returns rnk 1 and 2 naturally without needing special-case logic. If the requirement is to return exactly 3 tiers and pad with NULL for missing tiers, you would need a more complex solution using a generated series joined against the ranked results — but that is not typically what interview problems require.