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

TripJack's People Analytics team needs a department-level salary report for workforce planning. Write a PostgreSQL query that calculates the average salary for each department, but returns only departments with more than five employees.

Requirements

Join employees to departments using the department relationship.
Return the department name, employee count, and average salary.
Include only departments whose employee count is greater than 5, using an aggregate filter.
Sort the result alphabetically by department name. PostgreSQL's AVG should naturally ignore NULL salary values.

Schema

departments

Column	Type	Description
department_idPK	INT	Unique department identifier
department_name	VARCHAR(100)	Department name
office_location	VARCHAR(100)	Primary office location

employees

Column	Type	Description
employee_idPK	INT	Unique employee identifier
first_name	VARCHAR(80)	Employee first name
email	VARCHAR(150)	Employee work email
department_id	INT	Assigned department identifier
salary	NUMERIC(10,2)	Annual salary in Indian rupees