Advanced Salary Ranking SQL | Dataford Interview Questions

Your question is Advanced Salary Ranking SQL. Start with the requirements and the two tables on the right.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem

Decision Point wants to compare compensation among currently active employees. Write a PostgreSQL query that returns every active employee who earns the second highest distinct salary, along with their department.

Requirements

Consider only employees whose employment_status is Active and whose salary is not NULL.
Rank distinct salaries from highest to lowest using a window function. The query should be easy to adapt from the second highest salary to the Nth highest salary.
Return the employee ID, employee name, department name, and salary for all employees at the selected rank.
Order the output by employee ID ascending. Explain why DENSE_RANK is preferable to ROW_NUMBER when tied salaries should share a rank.

Schema

employees

Column	Type	Description
employee_idPK	INT	Unique employee identifier
employee_name	VARCHAR(100)	Employee's full name
department_id	INT	References departments.department_id
salary	NUMERIC(12,2)	Annual employee salary
employment_status	VARCHAR(20)	Current employment status

departments

Column	Type	Description
department_idPK	INT	Unique department identifier
department_name	VARCHAR(100)	Department display name