SQL: Third Largest | Dataford Interview Questions

Your question is SQL: Third Largest. 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

Xccelerated needs a salary benchmark for its active software engineering team. Write a PostgreSQL query to find the third-highest distinct annual salary among active Software Engineers whose department is present.

Requirements

Join employees to departments to include the department name.
Consider only employees with role = 'Software Engineer', employment_status = 'active', and a non-NULL salary.
Rank distinct salary values from highest to lowest using a window function, then return every employee tied at the third-highest salary.
Return employee_id, employee_name, department_name, annual_salary, and the rank, ordered by employee_id.

Schema

employees

Column	Type	Description
employee_idPK	INT	Unique employee identifier
employee_name	VARCHAR(100)	Employee full name
role	VARCHAR(100)	Employee job role
employment_status	VARCHAR(20)	Current employment status
annual_salary	NUMERIC(12,2)	Annual salary amount
department_id	INT	Referenced department identifier

departments

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