Rank by Calculated Compensation
Return first_name, last_name, salary, and calculated total_compensation, ordered from highest to lowest total compensation.
- CASE expressions
- Sorting
Exercise brief
Understand the request
Compensation modelling analyst A planning model adds a 10% bonus for department 1 and ranks employees by the resulting compensation.
IT employees (department_id = 1) get a 10% bonus on top of salary; everyone else gets no bonus. Show first_name, last_name, salary, and total_compensation = salary + (10% bonus only if IT) — sorted by total_compensation descending.
Return
- Calculate the department-specific bonus in the SELECT list.
- Sort by the calculated result descending.
Constraints
- Use CASE in the compensation expression.
- Sort by the total_compensation alias.
Data you will use
Review the relevant tables before deciding how to join, filter, or aggregate them.
employees
first_nameTEXTlast_nameTEXTsalaryDECIMALdepartment_idINTEGER
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
You can sort by any expression — column references, arithmetic, CASE, function calls, or a SELECT-list alias.
Hint 2
The CASE inside the calc only adds a bonus to IT (department_id = 1). Non-IT rows keep their raw salary.
Hint 3
ORDER BY total_compensation DESC (the alias works on most engines; otherwise repeat the expression)
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
SELECT first_name, last_name, salary, salary + CASE WHEN department_id = 1 THEN salary * 0.10 ELSE 0 END AS total_compensation FROM employees ORDER BY total_compensation DESC;Why this works
Compare with `ORDER BY salary * 12 DESC` — multiplying by a constant DOES NOT change the order, so that "calculated" sort is identical to `ORDER BY salary DESC`. The lesson here: a calculation is only meaningful when it can REORDER rows. The IT bonus does that — it lifts an IT mid-tier salary above some non-IT high salaries.
Success check
The output values and their descending calculated-compensation order both match.
Expected result
Use this output to verify values, aliases, ordering, and row count.
| first_name | last_name | salary | total_compensation |
|---|---|---|---|
| Mike | Johnson | 80000 | 88000 |
| Amy | Taylor | 78000 | 85800 |
| John | Doe | 75000 | 82500 |
| Alex | Miller | 72000 | 79200 |
| David | Brown | 70000 | 77000 |
| Chris | Anderson | 68000 | 68000 |
| Sarah | Williams | 65000 | 65000 |
| Rachel | Garcia | 64000 | 64000 |
| Tom | Wilson | 62000 | 62000 |
| Lisa | Davis | 58000 | 58000 |
Previewing 10 of 12 expected rows. Run the query in the editor to inspect the full result.
Learn the concepts behind this answer
Strengthen your understanding with these targeted learning topics:
Continue practicing
Build the next SQL skill
LIMIT & OFFSET
Practice deterministic top-N, cutoff ties, offset pagination, composite keyset cursors, and resumable bounded batches.
Ranking & NTH Value
Solve deterministic ranking, top-N, distribution, positional-frame, and rolling-window problems.
SELECT Statements
Select columns, filter rows, remove duplicates, and order query results.
Open the interactive workspace and practice across SQL topics.