Average salary by department

SQL data engineering interview problem. Difficulty: beginner. Pattern: GroupBy. About 8 minutes. Free to practice.

Management wants to compare teams. For each department in the employees table, they want to know how many people work there and what the average salary is. Return 1 row per department with 3 columns: department, employee_count and avg_salary (the average salary rounded to 2 decimal places). Leave out employees who have no department. Show the department with the highest average salary first. If two departments have the same average, sort them by name.

Requirements

  • Return 1 row for each department, not 1 row for each employee.
  • Group the rows by department.
  • Remove the rows where department is NULL before you group.
  • Sort by avg_salary from high to low, then by department from A to Z.

Constraints

  • The table is employees.
  • Return only these 3 columns, in this order: department, employee_count, avg_salary.
  • Round the average to 2 decimal places.

Examples

Input: employees department | salary Engineering | 120000 Engineering | 95000 Engineering | 110000 Engineering | 95000 Sales | 105000 Sales | NULL Output: department | employee_count | avg_salary Engineering | 4 | 105000.0 Sales | 2 | 105000.0 Why this passes: Both departments average 105000; Engineering sorts first by department name. NULL salary is skipped by AVG but counted in employee_count.

Topics: lakebench, sql, group by, avg.

More SQL interview questions · All interview problems · Learn data engineering

beginner

Average salary by department

Interview-style drill: Group employees by department with counts and average salary.

For each non-null `department` in `employees`, return: - `department` - `employee_count` - `avg_salary` as ROUND(AVG(salary), 2) Exclude rows where `department` IS NULL. Order by `avg_salary` descending, then `department`.