SQL Query for Above-Average Salary | Dataford Interview Questions

Your question is SQL Query for Above-Average Salary. Start with the requirements and the two tables on the right.

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

Problem

Dayforce workforce reporting needs to identify employees whose annual salary is above the company-wide average. Write a PostgreSQL query that returns these employees with their department information.

Requirements

Calculate the average of all non-NULL values in employees.annual_salary.
Return employees whose salary is strictly greater than that average.
Include employees even when they have no department assignment, using a LEFT JOIN.
Return results ordered from highest salary to lowest salary, with employee_id as a tie-breaker.

Schema

employees

Column	Type	Description
employee_idPK	INT	Unique employee identifier
employee_name	VARCHAR(100)	Employee's full name
department_id	INT	References the employee's department
annual_salary	NUMERIC(10,2)	Employee's annual salary

departments

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