Dept vs Company Pay
ASKED IN INTERVIEWDoor Dash
Problem Statement
Compare the average salary of each department to the overall company average salary.
Rules:
- Output columns: department_name, comparison_result.
- Comparison: Higher, Lower, or Same.
- Sort by department_name ASC.
Tests your understanding of
Window Functions, Case Logic and Aggregation
Input Tables
employees
| employee_id(INTEGER) | salary(INTEGER) | department_id(INTEGER) |
|---|---|---|
| 1 | 100000 | 1 |
| 2 | 120000 | 1 |
| 3 | 80000 | 2 |
| 4 | 90000 | 2 |
departments
| id(INTEGER) | name(VARCHAR) |
|---|---|
| 1 | Engineering |
| 2 | Marketing |
payroll_meta
| dept_id(INTEGER) | currency(VARCHAR) |
|---|---|
| 1 | USD |
Expected Output
| department_name(VARCHAR) | comparison_result(VARCHAR) |
|---|---|
| Engineering | Higher |
| Marketing | Lower |
Tags
HardASKED IN INTERVIEWWindow FunctionsCase LogicAggregation
20-30 min
42%