SQL Rank Per Department

EasySQL · PostgreSQL00:00

Practice interviewer

In session

5 left

00:00

Your question is SQL Rank Per Department. 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.

You need to log in / sign up to run or submit.

Problem

Infocepts Workforce Analytics needs a department-level salary benchmark. Write a PostgreSQL query to return the second-highest distinct salary for every department.

Requirements

Use DENSE_RANK() or an equivalent ranking approach so tied salaries share the same rank.
Exclude employees whose salary is NULL before ranking.
Return every department, including departments without a second distinct salary, with NULL in that result column.
Order the output by department ID.

Schema

departments

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

employees

Column	Type	Description
employee_idPK	INT	Unique employee identifier
employee_name	VARCHAR(100)	Employee full name
department_id	INT	Department assigned to the employee
salary	NUMERIC(12,2)	Annual employee salary

Tablesdepartmentsemployees

Your solutionPostgreSQL

You need to log in / sign up to run or submit.

Run a query to see results