Rank Rows With SQL Window Functions | Dataford Interview Questions

Tech Mahindra's workforce analytics team needs to compare active employees within their departments. Write a PostgreSQL query that ranks employees by salary within each department.

Requirements

Join employees to departments and include only employees with employment_status = 'Active'.
Use RANK() to rank salaries from highest to lowest independently within each department. Treat missing salaries as the lowest values.
Return department name, employee name, salary, and salary rank, ordered by department name, rank, and employee name.

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
department_id	INT	Employee's department identifier
employee_name	VARCHAR(100)	Employee's full name
salary	INT	Annual salary
employment_status	VARCHAR(20)	Current employment status

Your question is Rank Rows With SQL Window Functions. Start with the requirements and the two tables in the Question tab.

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

Problem