Apple-style Company ChallengeHardVerified answerSQLite live

Employees Earning Above Department Average

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

  • CTEs
  • Joins
  • Subqueries
  • Aggregation
  • Numeric functions

Challenge brief

Understand the request

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

Find employees above their department average using a CTE, showing salary difference.

Return

  • employee_name (full name)
  • department_name
  • salary
  • dept_avg_salary (rounded 2)
  • salary_difference (rounded 2)

Constraints

  • Return employees strictly above their own department average
  • Show the largest salary differences first and resolve ties by employee ID

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

You need the department average AND the employee salary in the same row. Compute department averages once in a CTE, then join back to the employees table for comparison.

Hint 2

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

Hint 3

Build question 17 from its business grain: identify the driving rows, add only valid relationships, then apply the required filtering, aggregation, and deterministic ordering.

Verified SQL answer

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

Reveal solution and explanation
WITH dept_avgs AS (SELECT department_id, ROUND(AVG(salary), 2) AS dept_avg FROM employees GROUP BY department_id) SELECT e.first_name || ' ' || e.last_name AS employee_name, d.department_name, e.salary, da.dept_avg AS dept_avg_salary, ROUND(e.salary - da.dept_avg, 2) AS salary_difference FROM employees e INNER JOIN departments d ON e.department_id = d.department_id INNER JOIN dept_avgs da ON e.department_id = da.department_id WHERE e.salary > da.dept_avg ORDER BY salary_difference DESC, e.employee_id

Why this works

The CTE computes one average per department. The main query attaches that average to every employee in that department, enabling the WHERE salary > dept_avg comparison. Tim Cook is the sole Executive employee — his salary equals the department average exactly, so he is excluded.

Success check

3 employees — Angela Ahrendts (+$62,500), Craig Federighi (+$56,667), Johnny Srouji (+$5,000)

Expected result

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

employee_namedepartment_namesalarydept_avg_salarysalary_difference
Angela AhrendtsRetail22000015750062500
Craig FederighiSoftware Engineering250000193333.3356666.67
Johnny SroujiHardware Engineering2400002350005000

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.