Objective

Write an SQL query to find employees whose salaries are above the average salary of their respective departments. The query should return the employee's name, department name, employee's salary, and the average salary of the department rounded to two decimal places. The results should be ordered by department name and then by employee salary in descending order.

Additional information

• Each department has a unique ID.
• Each employee belongs to only one department.
• The output should include only those employees who earn more than the average salary of their department.
• The salary values should be rounded to two decimal places in the output.
• The results must be sorted first by department name in ascending order, then by employee salary in descending order.

Constraints:

• The number of employees will be between 1 and 10^4.
• The number of departments will be between 1 and 10^3.

Clarifications:

• You can assume that the department names and employee names are unique within their respective tables.
• Locations of the departments are not relevant for this query.

WITH dept_avg AS (
    SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
)
SELECT
    e.name AS employee_name,
    d.name AS department_name,
    e.salary,
    ROUND(da.avg_salary, 2) AS department_avg_salary
FROM employees e
JOIN departments d  ON e.department_id = d.id
JOIN dept_avg da    ON e.department_id = da.department_id
WHERE e.salary > da.avg_salary
ORDER BY d.name, e.salary DESC;

Explanation

Step 1: Compute average salary per department (CTE)

dept_avg groups employees by department_id and computes AVG(salary) for each department, producing one row per department.

WITH dept_avg AS (
    SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
)

Step 2: Join employees, departments, and CTE; filter above-average earners

Two JOIN clauses link employees to their department name and to the precomputed average. WHERE e.salary > da.avg_salary retains only those earning more than their department mean.

JOIN departments d ON e.department_id = d.id
JOIN dept_avg da   ON e.department_id = da.department_id
WHERE e.salary > da.avg_salary

Step 3: Display results ordered by department then salary

ROUND(da.avg_salary, 2) formats the department average to two decimal places. ORDER BY d.name, e.salary DESC groups by department alphabetically and within each department shows the highest earner first.

ROUND(da.avg_salary, 2) AS department_avg_salary
ORDER BY d.name, e.salary DESC;