Third Highest Value with PySpark | Dataford Interview Questions

CGI Group needs a compensation report for active employees in Canadian offices. Write a PostgreSQL query that returns one salary value based on the distinct eligible salaries.

Requirements

Join employees to their departments and offices.
Include only employees with employment_status = 'Active', a non-NULL salary, and an office in Canada.
Rank distinct salaries from highest to lowest, so duplicate employee salaries count once.
Return the third-highest distinct salary when at least three exist. If exactly two exist, return the second-highest. If exactly one exists, return that salary. Return NULL when no eligible salary exists.
Return one row with the exact output column selected_salary.

Schema

employees

Column	Type	Description
employee_idPK	INT	Employee identifier
employee_name	VARCHAR(100)	Employee name
department_id	INT	References departments.department_id
salary	NUMERIC(12,2)	Annual employee salary
employment_status	VARCHAR(20)	Current employment status

departments

Column	Type	Description
department_idPK	INT	Department identifier
department_name	VARCHAR(100)	Department name
office_id	INT	References offices.office_id

offices

Column	Type	Description
office_idPK	INT	Office identifier
office_name	VARCHAR(100)	Office name
country	VARCHAR(50)	Office country
city	VARCHAR(80)	Office city

Your question is Third Highest Value with PySpark. Start with the requirements and the three 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