logo

Back to questions

Top Three Salaries FAANG SQL Interview Question

Top Three Salaries

FAANG SQL Interview Question

As part of an ongoing analysis of salary distribution within the company, your manager has requested a report identifying high earners in each department. A 'high earner' within a department is defined as an employee with a salary ranking among the top three salaries within that department.

You're tasked with identifying these high earners across all departments. Write a query to display the employee's name along with their department name and salary. In case of duplicates, sort the results of department name in ascending order, then by salary in descending order. If multiple employees have the same salary, then order them alphabetically.

Note: Ensure to utilize the appropriate ranking window function to handle duplicate salaries effectively.

As of June 18th, we have removed the requirement for unique salaries and revised the sorting order for the results.

Schema:

column_nametypedescription
employee_idintegerThe unique ID of the employee.
namestringThe name of the employee.
salaryintegerThe salary of the employee.
department_idintegerThe department ID of the employee.
manager_idintegerThe manager ID of the employee.

Example Input:

employee_idnamesalarydepartment_idmanager_id
1Emma Thompson380016
2Daniel Rodriguez223017
3Olivia Smith200018
4Noah Johnson680029
5Sophia Martinez1750111
6Liam Brown130003
7Ava Garcia125003
8William Davis68002
9Isabella Wilson110003
10James Anderson4000111

Schema:

column_nametypedescription
department_idintegerThe department ID of the employee.
department_namestringThe name of the department.

Example Input:

department_iddepartment_name
1Data Analytics
2Data Science

Example Output:

department_namenamesalary
Data AnalyticsJames Anderson4000
Data AnalyticsEmma Thompson3800
Data AnalyticsDaniel Rodriguez2230
Data ScienceNoah Johnson6800
Data ScienceWilliam Davis6800

The output displays the high earners in each department.

  • In the Data Analytics deaprtment, James Anderson leads with a salary of $4,000, followed by Emma Thompson earning $3,800, and Daniel Rodriguez with $2,230.
  • In the Data Science department, both Noah Johnson and William Davis earn $6,800, with Noah listed before William due to alphabetical ordering.

The dataset you are querying against may have different input & output - this is just an example!

Difficulty

Medium

PostgreSQL 14