Ranking Employees Within Departments | Dataford Interview Questions

Your question is Ranking Employees Within Departments. 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

HARMAN needs a salary ranking for active employees across departments such as Engineering, Product, and Quality. Write a PostgreSQL query that ranks employees within their respective departments.

Requirements

Join employees to departments and return the department name, employee ID, employee name, salary, and rank.
Include only employees whose employment status is Active and whose department exists.
Rank employees by salary in descending order within each department using RANK(). Employees with equal salaries must receive the same rank.
Place employees with a NULL salary after employees with known salaries, and order the final output by department, rank, and employee ID.

Schema

employees

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

departments

Column	Type	Description
department_idPK	INTEGER	Unique department identifier
department_name	VARCHAR(100)	HARMAN department name