SQL Queries for Ranking

MediumSQL · PostgreSQL00:00

Practice interviewer

In session

5 left

00:00

Your question is SQL Queries for Ranking. 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

HSBC wants to review compensation among active employees across its business divisions. Write a PostgreSQL query that ranks employees within each division and returns the highest and second-highest distinct annual salaries, including ties.

Requirements

Join employees to divisions and consider only active employees with a non-NULL salary.
Use a window function to rank salaries independently within each division.
Return all employees whose rank is 1 or 2, ordered by division, rank, and employee name.

Schema

divisions

Column	Type	Description
division_idPK	INT	Unique identifier for an HSBC business division
division_name	VARCHAR(100)	Business division name

employees

Column	Type	Description
employee_idPK	INT	Unique employee identifier
employee_name	VARCHAR(100)	Employee's full name
division_id	INT	References divisions.division_id
annual_salary	NUMERIC(12,2)	Annual salary in GBP
is_active	BOOLEAN	Whether the employee is currently active

Tablesdivisionsemployees

Your solutionPostgreSQL

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

Run a query to see results