Codetail — Practice Coding, Ship Faster

Window Functions for RankingHardWrite Code

5/5

ROW_NUMBER + PARTITION BY

Description

Write a query that ranks employees by salary within each department, and also shows the difference between each employee's salary and their department's average. **Schema:**

sql
employees(id, name, department, salary, hire_date)

This is a classic analytics query pattern used in dashboards and reporting.

Requirements

01Use ROW_NUMBER() or RANK() to rank employees by salary within each department

02Use AVG() as a window function to compute per-department average

03Calculate the difference between each employee's salary and department average

04Include employee name, department, salary, rank, and salary difference

05Order output by department, then by rank

Example

-- name       | department  | salary | dept_rank | dept_avg_salary | diff_from_avg
-- Alice      | Engineering | 145000 | 1         | 120000.00       | 25000.00
-- Bob        | Engineering | 130000 | 2         | 120000.00       | 10000.00
-- Charlie    | Engineering | 85000  | 3         | 120000.00       | -35000.00
-- Diana      | Marketing   | 95000  | 1         | 82500.00        | 12500.00
-- Eve        | Marketing   | 70000  | 2         | 82500.00        | -12500.00

Python 3

Ln 10, Col 1