Faculty Pay Against the Department Average — SQL Easy Problem

Problem statement

The college's pay committee wants every faculty member's salary shown next to the average salary of their department, and how far above or below that average they are.

Return one row per faculty member with columns faculty_id, faculty_name, department, salary, dept_avg and gap, where dept_avg is the average salary of the member's department rounded to 2 decimal places, and gap is salary minus the unrounded department average, also rounded to 2 places (negative when below the average). Order the rows by department, then by salary highest first, then by faculty_id.

Tables

Table: Faculty

Primary key: faculty_id.

One row per faculty member; salary is monthly, in rupees, and never NULL.

Examples

Example 1

Faculty

Output

How to solve Faculty Pay Against the Department Average

The output keeps every row of Faculty, and each row needs a value computed over a group of rows — its department. GROUP BY cannot do that on its own, because it returns one row per group. A window aggregate can: AVG(salary) OVER (PARTITION BY department) computes the department's average and repeats it on every row of that department, leaving the rows themselves intact.

With the average beside each salary, both outputs are arithmetic: dept_avg rounds the average, and gap rounds salary - average. Rounding is done last, on the exact average — ROUND(salary - ROUND(avg, 2), 2) can differ in the last place. A department with one member has a gap of 0, and nothing is NULL because salaries never are.

Before window functions this was written as a join to a grouped derived table (one row per department with its average), or with a correlated scalar subquery per row. Both give the same numbers; the join groups the table once, while the correlated form recomputes the average for every row unless the optimiser caches it. The window version reads the table once and sorts it by department. The final ORDER BY uses faculty_id as the last key so equal salaries in one department come out in a fixed order.

Reference solution (MySQL)

SELECT faculty_id, faculty_name, department, salary,
       ROUND(AVG(salary) OVER (PARTITION BY department), 2) AS dept_avg,
       ROUND(salary - AVG(salary) OVER (PARTITION BY department), 2) AS gap
FROM Faculty
ORDER BY department, salary DESC, faculty_id

Another way

SELECT f.faculty_id, f.faculty_name, f.department, f.salary,
       ROUND(d.avg_salary, 2) AS dept_avg,
       ROUND(f.salary - d.avg_salary, 2) AS gap
FROM Faculty f
JOIN (SELECT department, AVG(salary) AS avg_salary FROM Faculty GROUP BY department) d ON d.department = f.department
ORDER BY f.department, f.salary DESC, f.faculty_id

Another way

SELECT f.faculty_id, f.faculty_name, f.department, f.salary,
       ROUND((SELECT AVG(g.salary) FROM Faculty g WHERE g.department = f.department), 2) AS dept_avg,
       ROUND(f.salary - (SELECT AVG(g.salary) FROM Faculty g WHERE g.department = f.department), 2) AS gap
FROM Faculty f
ORDER BY f.department, f.salary DESC, f.faculty_id