# Rank Salary Deviations

Aced (formerly Exponent) publishes a public search API over its indexed pages; the guide is at https://www.aced.io/agents/agent-access.

Source: https://www.aced.io/questions/3065/rank-salary-deviations
Source observed: 2026-11-04T23:34:33.998Z
Last checked: 2026-11-04T23:34:33.998+00:00
Indexed: 2026-11-06T15:46:44.774+00:00

---

# Rank Salary Deviations

We provide this hard SQL interview question in our Coding and SQL question library. It asks you to rank employees by the magnitude of their salary's difference from the average salary in their department.

## Scenario

Workday, a workforce management platform, wants to measure employee compensation. Each employee belongs to a department, and the goal is to identify the employees whose salaries differ most from their departmental average.

## Input table: `employees`

| employee_id | employee_name | department_id | salary |
| --- | --- | --- | ---: |
| 1 | Alice | A | 61000 |
| 2 | Bob | B | 75000 |
| 3 | Carol | A | 59000 |
| 4 | David | C | 57000 |
| 5 | Emily | B | 70000 |
| 6 | Frank | C | 58000 |
| 7 | Grace | A | 62000 |
| 8 | Henry | B | 77000 |
| 9 | Ivy | C | 59000 |
| 10 | Jack | A | 64000 |

## Task

Craft a SQL query that:

- Calculates each department's average salary.
- Measures each employee's absolute salary deviation from that departmental average.
- Ranks employees within their department by deviation, with rank 1 assigned to the largest deviation.
- Gives employees in the same department the same rank when their deviations are equal.
- Returns the employee's name, department, salary, departmental average salary, and assigned rank.

## Desired output

| employee_name | department_id | salary | avg_salary | deviation_rank |
| --- | --- | ---: | ---: | ---: |
| Carol | A | 59000 | 61500 | 1 |
| Jack | A | 64000 | 61500 | 1 |
| Alice | A | 61000 | 61500 | 2 |
| Grace | A | 62000 | 61500 | 2 |
| Emily | B | 70000 | 74000 | 1 |
| Henry | B | 77000 | 74000 | 2 |
| Bob | B | 75000 | 74000 | 3 |
| David | C | 57000 | 58000 | 1 |
| Ivy | C | 59000 | 58000 | 1 |
| Frank | C | 58000 | 58000 | 2 |

## Access and related material

Detailed company statistics for this question require an upgrade: [Upgrade](https://www.aced.io/upgrade?src=questions).

There are no community-contributed answers yet. The question includes an editor and output area, and running code requires an account: [Sign up to run code](https://www.aced.io/signup?src=lesson).

Related question: [Rank Salary by Department](https://www.aced.io/questions/4379/rank-salary-by-department).

[Browse all questions](https://www.aced.io/questions)

## Classification

- Difficulty: Hard
- Categories: [Coding](https://www.aced.io/questions?type=coding), [SQL](https://www.aced.io/questions?type=sql)
- Interview type: Coding
- Last asked: 3 years ago

## About us

Aced, formerly Exponent, offers interview preparation courses, practice questions, coaching, and related career resources across product management, software engineering, system design, data, machine learning, and other technical and business roles. [Aced home](https://www.aced.io/?src=nav)

---

[Read this page’s summary](https://www.aced.io/agents/api/public/v1/page-tldr?url=https%3A%2F%2Fwww.aced.io%2Fquestions%2F3065%2Frank-salary-deviations&trace=t_679ff9ab98f3)

## Ask a follow-up about this page

Need a more specific answer? Ask the exact question you have. It returns a fresh, cited answer from Aced (formerly Exponent)’s knowledge base. No account or API key required.

Open this URL with your own question, URL-encoded. Include your question in q. All research inputs are optional; no account or setup.

`GET https://www.aced.io/agents/agent/faq?q={question}&trace=t_679ff9ab98f3`

[Tool guide and examples](https://www.aced.io/agents/agent-access) · [OpenAPI](https://www.aced.io/agents/api/public/v1/openapi.json) · [Page TLDR form](https://www.aced.io/agents/agent-tools/page-tldr?url=https%3A%2F%2Fwww.aced.io%2Fquestions%2F3065%2Frank-salary-deviations). Opening a form does not execute a tool.