Department salary maximum, average, and count
For each non-NULL department ID represented by employees, return employee count, maximum salary, and average salary rounded to two decimals. COUNT includes employees with NULL salary; aggregates ignore NULL salaries. Return only the requested columns and follow the specified ordering. This question probes group once and compute all aggregates; avg ignores nulls while count(*) counts rows.
Input
Setup script defining employees(id INT, name VARCHAR(30), department_id INT, manager_id INT, salary INT).
Output
department_id, employee_count, max_salary, avg_salary; ascending department_id.
Constraints
0–10000 rows per table. IDs are unique and non-NULL unless stated otherwise; foreign keys may be NULL. Numeric amounts are integers from 0 to 1000000. Dates are valid YYYY-MM-DD text. Text uses case-sensitive comparison; test data has consistent case. Duplicate business values and NULLs are allowed unless excluded by the task.
Example
Tables and sample rows
CREATE TABLE employees(id INT, name VARCHAR(30), department_id INT, manager_id INT, salary INT); INSERT INTO employees VALUES (1,'Ana',10,NULL,100),(2,'Ben',10,1,200),(3,'Cara',20,1,200),(4,'Dan',20,3,300),(5,'Eve',NULL,NULL,NULL);
Expected result
department_id | employee_count | max_salary | avg_salary 10 | 2 | 200 | 150.0 20 | 2 | 300 | 250.0
Another dataset (1)
CREATE TABLE employees(id INT, name VARCHAR(30), department_id INT, manager_id INT, salary INT); INSERT INTO employees VALUES (1,'Solo',10,NULL,100),(2,'Twin',10,1,100),(3,'Null',NULL,NULL,NULL);
Expected result
department_id | employee_count | max_salary | avg_salary 10 | 2 | 100 | 100.0
Covers missing matches, ties, or NULL values without relying on insertion order.
Hints (2)
- Exclude NULL department IDs.
- Use AVG(salary), not SUM divided by COUNT(*).
Target complexity: time O(n) · space O(n)