Back to questions
Second Highest Salary [FAANG SQL Interview Question]
Imagine you're an HR analyst at a tech company tasked with analyzing employee salaries. Your manager is keen on understanding the pay distribution and asks you to determine the second highest salary among all employees.
It's possible that multiple employees may share the same second highest salary. In case of duplicate, display the salary only once.
Schema:
| column_name | type | description |
|---|---|---|
| employee_id | integer | The unique ID of the employee. |
| name | string | The name of the employee. |
| salary | integer | The salary of the employee. |
| department_id | integer | The department ID of the employee. |
| manager_id | integer | The manager ID of the employee. |
Example Input:
| employee_id | name | salary | department_id | manager_id |
|---|---|---|---|---|
| 1 | Emma Thompson | 3800 | 1 | 6 |
| 2 | Daniel Rodriguez | 2230 | 1 | 7 |
| 3 | Olivia Smith | 2000 | 1 | 8 |
Example Output:
| second_highest_salary |
|---|
| 2230 |
The output represents the second highest salary among all employees. In this case, the second highest salary is $2,230.
The dataset you are querying against may have different input & output - this is just an example!
Solution
Step 1: Find the Highest Salary
First, we determine the highest salary from the table using aggregate function. Why? We need to know the highest salary to identify the second highest salary. Learn about the function here.
Step 2: Find the Second Highest Salary
Subsequently, we use the result of the query in Step 1 as a subquery. It selects the maximum salary from the table where the salary is less than the highest salary obtained in the subquery. By comparing salaries to the maximum salary from Step 1, we can exclude the highest salary itself and identify the second highest salary.
Method 2: Using CTE
Alternatively, you can utilize a CTE instead of a subquery.
PostgreSQL 14