Amazon-style Company ChallengeMediumVerified answerSQLite live

Employees Earning Above Department Average

Which employees earn more than their department's average salary, and by how much?

  • CTEs
  • Joins
  • Subqueries
  • Aggregation
  • Numeric functions

Challenge brief

Understand the request

Compensation Team is preparing a pay equity review and needs to identify employees who earn above their department benchmark.

Find employees earning above their department average salary using a CTE.

Return

  • employee_name (full name)
  • department_name
  • employee_salary
  • dept_avg_salary (rounded to 2 decimals)
  • salary_difference

Constraints

  • Only show employees who are strictly above the department average
  • Compare each employee with the average for that employee's own department
  • Order by salary_difference descending

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
  • 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

The challenge is that you need the department average AND the individual salary in the same row for comparison. Compute department averages once in a CTE, then join that CTE back to the employees table.

Hint 2

CTE: SELECT department_id, ROUND(AVG(salary), 2) AS dept_avg_salary FROM employees GROUP BY department_id. Main query: join employees to departments and to the CTE on department_id. WHERE e.salary > da.dept_avg_salary.

Hint 3

Scaffold: compute one average per department, join it back to employees and departments, retain positive salary differences, and order the gaps descending.

Verified SQL answer

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

Reveal solution and explanation
WITH department_averages 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, d.department_name, e.salary AS employee_salary, da.dept_avg_salary, e.salary - da.dept_avg_salary AS salary_difference FROM employees e INNER JOIN departments d ON e.department_id = d.department_id INNER JOIN department_averages da ON e.department_id = da.department_id WHERE e.salary > da.dept_avg_salary ORDER BY salary_difference DESC;

Why this works

The CTE computes one average per department. The main query joins each employee to their department average for a side-by-side comparison. Jeff Bezos is excluded because he IS the only Executive — his salary equals the department average exactly.

Success check

3 employees — Andy Jassy (7,500 above avg), Sarah Connor (1,800 above avg), Lisa Wang (,500 above avg)

Expected result

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

employee_namedepartment_nameemployee_salarydept_avg_salarysalary_difference
Andy JassyEngineering18000013250047500
Sarah ConnorOperations850005320031800
Lisa WangEngineering1350001325002500

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.