SQL And Spark Querying | Dataford Interview Questions

Context

Exusia's Data Fabric team needs a repeatable way to identify the highest-paid employees while retaining department information when available. The result must handle salary ties and employees whose department reference does not match a department record.

Task

Write a PostgreSQL query that returns every employee earning the maximum salary in the employees table. Also describe how to produce the same result with a DataFrame and an RDD.

Requirements

Use a CTE to calculate the overall maximum non-NULL salary.
Return every employee tied at that salary, including the employee name and salary.
Use a LEFT JOIN to include the employee when its department reference has no matching department.
Order the result by employee name.

Schema

employees

Column	Type	Description
employee_idPK	INT	Unique employee identifier
employee_name	VARCHAR(100)	Employee full name
salary	NUMERIC(12,2)	Annual salary
department_id	INT	Referenced department identifier
job_title	VARCHAR(100)	Current job title

departments

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

Your question is SQL And Spark Querying. 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