SQL Above Department Average | Dataford Interview Questions

Amazon workforce planning needs to identify employees whose salary is greater than the average salary for their department. Write a PostgreSQL query using the employee and department data.

Requirements

Calculate the average salary independently for each department, ignoring NULL salaries.
Return employees whose salary is strictly greater than their department average.
Include the employee ID, employee name, department name, and salary, ordered by department name and salary descending.
Exclude employees with no matching department or no salary.

Schema

employees

Column	Type	Description
employee_idPK	INT	Unique employee identifier
employee_name	VARCHAR(100)	Employee name
department_id	INT	References departments.department_id
salary	DECIMAL(12,2)	Annual employee salary

departments

Column	Type	Description
department_idPK	INT	Unique department identifier
department_name	VARCHAR(100)	Department name

Your question is SQL Above Department Average. Start with the requirements and the two tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem