Top N by a Calculated Expression
Return the top 3 employees by annual salary (salary × 12).
- CTEs
- CASE expressions
- Sorting
- Top-N
Interview brief
Understand the request
Compensation modelling analyst The top-earner card shows annual salary and limits to the highest-value employees.
IT employees (department_id = 1) get a 10% bonus on top of salary; everyone else gets no bonus. Find the top 4 employees by total compensation = salary + bonus, with a deterministic tie-breaker. Show first_name, last_name, salary, total_compensation, employee_id.
Return
- Return first_name, last_name, salary, and annual_salary.
Constraints
- Order by the calculated field and limit to 3 rows.
Data you will use
Review the relevant tables before deciding how to join, filter, or aggregate them.
employees
first_nameTEXTlast_nameTEXTsalaryDECIMALdepartment_idINTEGERemployee_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 — including a CASE. LIMIT then keeps the top N.
Hint 2
The bonus only applies to IT, so the Top 4 by total_compensation is NOT identical to the Top 4 by salary.
Hint 3
ORDER BY total_compensation DESC, employee_id LIMIT 4
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, employee_id FROM employees ORDER BY total_compensation DESC, employee_id LIMIT 4;Why this works
Compare with `ORDER BY salary * 12 DESC LIMIT 4` — multiplying by a positive constant doesn't change the order, so the calculation is functionally a no-op. A meaningful calculated sort must be able to REORDER rows. The IT bonus does that.
Expected result
Use this output to verify values, aliases, ordering, and row count.
| first_name | last_name | salary | total_compensation | employee_id |
|---|---|---|---|---|
| Mike | Johnson | 80000 | 88000 | 3 |
| Amy | Taylor | 78000 | 85800 | 8 |
| Kevin | Martinez | 76000 | 83600 | 13 |
| John | Doe | 75000 | 82500 | 1 |
Learn the concepts behind this answer
Strengthen your understanding with these targeted learning topics: