Show Each Salary Against the Company Average
Use a scalar subquery in the SELECT list to calculate company_avg and salary_minus_avg for every employee.
- Subqueries
- Aggregation
- Sorting
Exercise brief
Understand the request
Compensation reporting lead A review worksheet needs row-level salary, company average, and the signed difference on the same row.
Show every employee with their salary, the company average, and the delta (salary − avg). The company average is computed via a scalar subquery in the SELECT list. Return employee_id, first_name, last_name, salary, company_avg, salary_minus_avg — ordered by salary DESC, employee_id.
Return
- Return employee_id, first_name, last_name, salary, company_avg, salary_minus_avg in this exact left-to-right order.
Constraints
- Keep one row per employee.
- The company average must come from a SELECT-list scalar subquery.
Data you will use
Review the relevant tables before deciding how to join, filter, or aggregate them.
employees
employee_idINTEGERfirst_nameVARCHAR(50)last_nameVARCHAR(50)salaryINTEGER
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
Same scalar subquery can be reused — the engine caches the value (uncorrelated).
Hint 2
The cleanest way to show 'this row vs the global aggregate' on a single result set.
Hint 3
Modern alternative: a window aggregate `AVG(salary) OVER ()` (no PARTITION BY) — same effect.
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
SELECT e.employee_id, e.first_name, e.last_name, e.salary, (SELECT AVG(salary) FROM employees) AS company_avg, e.salary - (SELECT AVG(salary) FROM employees) AS salary_minus_avg FROM employees e ORDER BY e.salary DESC, e.employee_id;Why this works
Scalar subqueries in SELECT are perfect for 'compare each row to a global metric'. They are uncorrelated, so they run once and cache. Window aggregates with empty OVER() are the modern equivalent.
Success check
Every employee appears once with the same computed average and a correct signed delta.
Expected result
Use this output to verify values, aliases, ordering, and row count.
| employee_id | first_name | last_name | salary | company_avg | salary_minus_avg |
|---|---|---|---|---|---|
| 100 | John | Smith | 120000 | 78800 | 41200 |
| 105 | Emma | Taylor | 95000 | 78800 | 16200 |
| 107 | Grace | White | 90000 | 78800 | 11200 |
| 101 | Alice | Johnson | 85000 | 78800 | 6200 |
| 102 | Bob | Wilson | 80000 | 78800 | 1200 |
| 104 | David | Brown | 70000 | 78800 | -8800 |
| 109 | Ivy | Martinez | 68000 | 78800 | -10800 |
| 106 | Frank | Green | 65000 | 78800 | -13800 |
| 103 | Carol | Davis | 60000 | 78800 | -18800 |
| 108 | Henry | Clark | 55000 | 78800 | -23800 |
Learn the concepts behind this answer
Strengthen your understanding with these targeted learning topics:
Continue practicing
Build the next SQL skill
CTEs & Window Functions
Practice modular CTE pipelines, deterministic window analytics, period comparisons, deduplication, frames, and gaps-and-islands.
SQL Joins
Practice reliable INNER, LEFT, FULL, CROSS, self, semi, anti, range, temporal, and many-to-many join patterns.
SQL Aggregations
Build reliable SQL metrics from aggregate functions through grain, fan-out, weighted ratios, rollups, percentiles, and approximate counts.
Open the interactive workspace and practice across SQL topics.