SQL Average Salary by Department | Dataford Interview Questions

Your question is SQL Average Salary by Department. 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

Hays wants a department-level compensation summary for its workforce analytics reporting. Write a PostgreSQL query that calculates the average salary of active employees in every department.

Requirements

Return every department, including departments with no active employees.
Calculate the average of salary for active employees only. PostgreSQL AVG should ignore NULL salaries.
Round the average salary to two decimal places and return it as average_salary.
Sort the output by department_id in ascending order.

Schema

departments

Column	Type	Description
department_idPK	INTEGER	Unique department identifier
department_name	VARCHAR(100)	Hays department name

employees

Column	Type	Description
employee_idPK	INTEGER	Unique employee identifier
employee_name	VARCHAR(100)	Employee name
department_id	INTEGER	Assigned department identifier
salary	NUMERIC(12,2)	Annual employee salary
employment_status	VARCHAR(20)	Current employment status