Advanced Window Functions | Dataford Interview Questions

Write a SQL query using advanced window functions such as DENSE_RANK and RANK(). Asked in the Technical Round 2 stage. This was the first SQL question of round 2.

Use the provided employee, department, and assessment score data. Include active employees assigned to a department, treating missing completed scores as zero.

Output

One row per included employee with department_name, employee_name, total_points, dense_rank, and rank.
Rank employees within each department by total points descending. Tied employees share both ranks.
Order by department_name, rank, then employee_id.

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
employee_name	VARCHAR(100)	Employee name
department_id	INT	Assigned department identifier
is_active	BOOLEAN	Whether the employee is active

assessment_scores

Column	Type	Description
score_idPK	INT	Unique assessment score identifier
employee_id	INT	Employee receiving the score
points	INT	Points earned for the assessment
completed	BOOLEAN	Whether the assessment was completed

Your question is Advanced Window Functions. 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