Dept Salary Comparison
Google
Problem Statement
Google HR wants to identify high-earners relative to their peers. Your task is to find all employees who earn more than the average salary of their department.
Rules:
- Output columns: employee_id, name, salary, department_id.
- Results must be sorted by employee_id in ascending order.
Tests your understanding of
Window Functions, Subqueries and Aggregation
Input Tables
Employees
| employee_id(INTEGER) | name(VARCHAR) | salary(INTEGER) | department_id(INTEGER) |
|---|---|---|---|
| 1 | Alice | 90000 | 1 |
| 2 | Bob | 70000 | 1 |
| 3 | Charlie | 80000 | 2 |
| 4 | David | 110000 | 2 |
| 5 | Eve | 105000 | 2 |
Departments
| department_id(INTEGER) | dept_name(VARCHAR) |
|---|---|
| 1 | Engineering |
| 2 | Marketing |
Bonus_Log
| employee_id(INTEGER) | bonus_amount(INTEGER) |
|---|---|
| 1 | 5000 |
Expected Output
| employee_id(INTEGER) | name(VARCHAR) | salary(INTEGER) | department_id(INTEGER) |
|---|---|---|---|
| 1 | Alice | 90000 | 1 |
| 4 | David | 110000 | 2 |
| 5 | Eve | 105000 | 2 |
Tags
MediumWindow FunctionsSubqueriesAggregation
20-25 min
51%