Employee salary aggregates

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

HR wants one quick summary of the whole company's pay. Instead of a list of people, you should return a single row of totals. From the employees table, return 1 row with 4 columns: employee_count (how many employee rows there are), avg_salary (the average salary, rounded to 2 decimal places), min_salary (the lowest salary) and max_salary (the highest salary). Some employees do not have a salary yet. AVG, MIN and MAX skip those rows on their own, but COUNT(*) counts every row.

Requirements

• Return exactly 1 row.
• employee_count must count every employee, including the ones with no salary. COUNT(*) does this.
• avg_salary must be rounded to 2 decimal places with ROUND.
• Do not use GROUP BY. You want one total for the whole table, not one per group.

Constraints

• The table is employees.
• Return only these 4 columns, in this order: employee_count, avg_salary, min_salary, max_salary.
• At least one employee has a salary, so the average always exists.

Examples

Input: employees name | salary Alice Chen | 120000 Bob Kumar | 95000 Dan Park | 110000 Eve Ng | 95000 Omar Ali | 105000 Max Reed | NULL Output: employee_count | avg_salary | min_salary | max_salary 6 | 105000.0 | 95000 | 120000 Why this passes: COUNT(*) includes the NULL-salary row. AVG/MIN/MAX skip NULL over the five paid salaries.

Topics: lakebench, sql, agg, avg.

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