Employee Salary Stats per Department

Preview mode. Log in to edit, run, submit, and save progress.

Medium

Description

You are given a table of employees. Each employee has a name, a department, a salary, and a hire date. Write a SQL query to find for each department: the number of employees, the average salary (rounded to 2 decimal places), the maximum salary, and the minimum salary. Return the result ordered by department name. Table: Employees

Column Name	Type	Description
id	INT	Primary key
name	VARCHAR	Employee name
department	VARCHAR	Department name
salary	INT	Annual salary
hire_date	DATE	Date of hire

Database Schema (Inferred)

Employees

Column Name	Example Value
id	1
name	Alice
department	Engineering
salary	90000
hire_date	2020-01-10

Example

Employees

id	name	department	salary	hire_date
1	Alice	Engineering	90000	2020-01-10
2	Bob	Engineering	85000	2019-06-15
3	Carol	Marketing	70000	2021-03-01
4	Dave	Marketing	65000	2022-07-20
5	Eve	HR	60000	2023-01-05

Output

department	employee_count	avg_salary	max_salary	min_salary
Engineering	2	87500	90000	85000
HR	1	60000	60000	60000
Marketing	2	67500	70000	65000

Explanation:

Group by department. Count employees, compute AVG/MAX/MIN of salary, and round AVG to 2 decimal places.

Approach hint

Start with a simple approach, explain the trade-off, then move toward a cleaner or more scalable solution.

Common mistake

Skipping assumptions, edge cases, or trade-offs can make an otherwise good answer feel incomplete.