SQL Window Functions and CTEs | Dataford Interview Questions

Describe your approach to writing a SQL query using window functions and Common Table Expressions to derive ranked performance metrics.

Use employees and performance_scores for the 2025-Q1 reporting period. Treat missing scores as zero for total-score ranking and exclude employees without a 2025-Q1 performance record.

Output

One row per included employee with employee_name, department, review_count, total_score, average_score, and department_rank.
Rank employees within each department by descending total score. Ties share the same rank.
Order by department, department rank, and employee ID.

Schema

employees

Column	Type	Description
employee_idPK	INT	Unique employee identifier
employee_name	VARCHAR(100)	Employee display name
department	VARCHAR(80)	Employee department

performance_scores

Column	Type	Description
score_idPK	INT	Unique performance score identifier
employee_id	INT	Referenced employee
review_period	VARCHAR(20)	Reporting period for the score
score	DECIMAL(6,2)	Performance score for one review metric

Your question is SQL Window Functions and CTEs. 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