Skip to content
LakeBench
ProblemsCommunityPricing
Sign inStart practicing

Average salary by department

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

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.

Requirements

  • One row per named department, richest average first.

Constraints

  • ROUND average to 2 decimals.
  • Return exactly these columns: department, employee_count, avg_salary.

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`.