Amazon-style Company ChallengeHardVerified answerSQLite live

Department Hierarchy and Salary Analysis

For every employee, show their manager's name, department, their salary gap vs their manager, their salary rank within their department, and the department average salary.

  • CTEs
  • Window functions
  • Joins
  • Subqueries
  • Aggregation

Challenge brief

Understand the request

People Analytics is running a compensation benchmarking exercise and needs a full view of salary hierarchy, manager comparisons, and department standings.

Build a salary hierarchy report with manager comparison, department rank, and department average using a CTE and self-join.

Return

  • employee_name
  • manager_name (NULL for top-level)
  • department_name
  • employee_salary
  • manager_salary (NULL for top-level)
  • salary_difference (manager salary minus employee salary, 0 if no manager)
  • dept_salary_rank (rank within department, 1 = highest)
  • dept_avg_salary

Constraints

  • Include every employee, including top-level employees with no manager
  • Salary rank resets within each department and preserves ties
  • Report a zero manager salary difference when no manager exists
  • Return departments alphabetically and employees by department salary rank

Data you will use

Review the relevant tables before deciding how to join, filter, or aggregate them.

employees

  • employee_idINTEGER
  • first_nameVARCHAR(50)
  • last_nameVARCHAR(50)
  • salaryINTEGER
  • manager_idINTEGER
  • department_idINTEGER

departments

  • department_idINTEGER
  • department_nameVARCHAR(100)

Hints, when you need them

Open one clue at a time so you still do the reasoning.

Hint 1

This query combines 3 techniques: (1) a CTE for department averages, (2) a self-join to look up the manager's name and salary from the same employees table, (3) RANK() OVER (PARTITION BY ...) to rank within each department. Build each piece separately before combining.

Hint 2

CTE: SELECT department_id, ROUND(AVG(salary),2) AS dept_avg_salary FROM employees GROUP BY department_id. Main query: alias employees twice — e for the employee, m for the manager. LEFT JOIN e to m ON e.manager_id = m.employee_id. RANK() OVER (PARTITION BY e.department_id ORDER BY e.salary DESC).

Hint 3

Scaffold: compute department averages first, self-join employees to managers without dropping leaders, then add a department-partitioned salary rank.

Verified SQL answer

Attempt the problem first, then compare structure and reasoning—not just syntax.

Reveal solution and explanation
WITH dept_stats AS (SELECT department_id, ROUND(AVG(salary), 2) AS dept_avg_salary FROM employees GROUP BY department_id) SELECT e.first_name || ' ' || e.last_name AS employee_name, m.first_name || ' ' || m.last_name AS manager_name, d.department_name, e.salary AS employee_salary, m.salary AS manager_salary, COALESCE(m.salary - e.salary, 0) AS salary_difference, RANK() OVER (PARTITION BY e.department_id ORDER BY e.salary DESC) AS dept_salary_rank, ds.dept_avg_salary FROM employees e LEFT JOIN employees m ON e.manager_id = m.employee_id INNER JOIN departments d ON e.department_id = d.department_id INNER JOIN dept_stats ds ON e.department_id = ds.department_id ORDER BY d.department_name, dept_salary_rank;

Why this works

PARTITION BY department_id in the window function resets the rank counter for each department — so both Engineering and Operations have a rank 1. COALESCE handles top-level employees (Jeff Bezos) who have no manager: m.salary is NULL, so COALESCE returns 0. LEFT JOIN keeps Jeff even though he has no manager row.

Success check

10 rows — all employees with hierarchy context, sorted by department then rank

Expected result

Use this output to verify values, aliases, ordering, and row count.

employee_namemanager_namedepartment_nameemployee_salarymanager_salarysalary_differencedept_salary_rankdept_avg_salary
Andy JassyJeff BezosEngineering180000200000200001132500
Lisa WangAndy JassyEngineering135000180000450002132500
John SmithAndy JassyEngineering120000180000600003132500
David LeeAndy JassyEngineering95000180000850004132500
Jeff BezosNULLExecutive200000NULL01200000
Sarah ConnorAndy JassyOperations8500018000095000153200
Rachel GreenSarah ConnorOperations480008500037000253200
Tom WilsonSarah ConnorOperations460008500039000353200
Emily DavisSarah ConnorOperations450008500040000453200
Michael BrownSarah ConnorOperations420008500043000553200

Learn the concepts behind this answer

Strengthen your understanding with these targeted learning topics:

Continue practicing

Explore related company challenges

SQL Interview Practice →

Return to the complete interview preparation experience.