Employees paid above their department's average

Step 1 Solve it yourself first

Tables you will use

employees

What it really tests

Comparing a row with an aggregate of its own group — three valid ways (window function, join to a grouped subquery, correlated subquery) and knowing when each fits.

The pattern: Compare a row with its group

"Above the average for their…", "more than their team's…", "compared with others in the same…".

Picture it

flowchart LR
  E["each employee"] --> A["AVG(salary) OVER<br/>(PARTITION BY dept_id)"]
  A --> C{"salary above<br/>dept average?"}
  C -->|"yes"| K["Asha, Hana, Dara"]

The approach, step by step

1. Work out each department's average salary.
2. Put that average next to each employee — a window function does both in one step.
3. Keep rows where salary > the department average, filtering outside the CTE.
4. Decide what to do with Gus, who has no department.

WITH with_avg AS (
  SELECT name, dept_id, salary,
         AVG(salary) OVER (PARTITION BY dept_id) AS dept_avg
  FROM employees
)
SELECT name, dept_id, salary, dept_avg
FROM with_avg
WHERE salary > dept_avg
ORDER BY dept_id, salary DESC;

SELECT e.name, e.dept_id, e.salary, a.dept_avg
FROM employees e
JOIN (
  SELECT dept_id, AVG(salary) AS dept_avg
  FROM employees
  GROUP BY dept_id
) AS a ON a.dept_id = e.dept_id
WHERE e.salary > a.dept_avg
ORDER BY e.dept_id, e.salary DESC;

Step 2 Go further (free, optional)

Optional. The notes above are written to be enough on their own. These free resources give a second explanation and more practice.

• Read

  PostgreSQL docs — Window Functions (tutorial)

  official documentation, free

• Practice

  DataLemur — window function questions

  real interview questions, many free

Step 3 Practise

Try each one yourself first: out loud, or written and run in the practice box below, which saves every query you run. Then open the answer and compare.

1. ExplainSay what it does and when to use it, in plain words, with no notes.

   Explain the three ways to compare a row with its group's average.

   Show answer

   1. A window function: AVG(salary) OVER (PARTITION BY dept_id) puts the department average on every row; a CTE then filters salary > dept_avg. One pass, and the average is right there to show.
   2. Join to a grouped subquery: SELECT dept_id, AVG(salary) … GROUP BY dept_id makes one row per department, then you join it to employees on dept_id and compare. Works in every database, even ones without window functions.
   3. A correlated subquery: WHERE salary > (SELECT AVG(salary) FROM employees e2 WHERE e2.dept_id = e.dept_id). Reads naturally, but is worked out once per row (good optimisers rewrite it), and the average isn't available to display.

2. Writefrom a blank page, then run it

   List orders whose amount is above that customer's average order amount.

   Show answer

   Partition the average by customer instead of department. Priya averages 85 (425 ÷ 5), so orders 101 (120) and 105 (150) are above it; Tom averages 137.5, so 104 (200) is. Lena's average is 60 (the NULL order is skipped), and her only amount equals it, so nothing of hers is above.

   sqlCopy

   WITH with_avg AS (
     SELECT order_id, customer_id, amount,
            AVG(amount) OVER (PARTITION BY customer_id) AS customer_avg
     FROM orders
   )
   SELECT order_id, customer_id, amount, customer_avg
   FROM with_avg
   WHERE amount > customer_avg
   ORDER BY customer_id, order_id;

   Try it

   3 rows

   order_id	customer_id	amount	customer_avg
   101	1	120	85
   105	1	150	85
   104	2	200	137.5

3. Predictsay it before you run it

   If Chen's salary rose to 90000, who would the solution return?

   Show answer

   Asha, Chen and Dara. Engineering's average rises to (95,000 + 70,000 + 90,000 + 80,000) ÷ 4 = 83,750. Asha (95,000) and Chen (90,000) are above it; Hana (80,000) drops below. Sales is unchanged, so Dara is still above 52,500.

   sqlCopy

   UPDATE employees SET salary = 90000 WHERE name = 'Chen';
   WITH with_avg AS (
     SELECT name, dept_id, salary,
            AVG(salary) OVER (PARTITION BY dept_id) AS dept_avg
     FROM employees
   )
   SELECT name, dept_id, salary, dept_avg
   FROM with_avg
   WHERE salary > dept_avg
   ORDER BY dept_id, salary DESC;

   Try it

   3 rows

   name	dept_id	salary	dept_avg
   Asha	1	95000	83750
   Chen	1	90000	83750
   Dara	2	60000	52500

4. Edge casesSay what happens with NULLs, ties, duplicates or no rows at all, and fix it.

   Make the solution exclude employees with no department, and explain why the join version already does.

   Show answer

   Add WHERE dept_id IS NOT NULL inside the CTE, so Gus is never given a "department" of his own. (In the window version, PARTITION BY puts all NULLs in one group, so Gus is compared with himself; he isn't above his own average, so he doesn't appear today, but only by luck: two employees with no department would be compared with each other.)

   The join version excludes him already: it joins on a.dept_id = e.dept_id, and NULL = NULL is not true, so Gus's row finds no partner and the join drops it.

   sqlCopy

   WITH with_avg AS (
     SELECT name, dept_id, salary,
            AVG(salary) OVER (PARTITION BY dept_id) AS dept_avg
     FROM employees
     WHERE dept_id IS NOT NULL
   )
   SELECT name, dept_id, salary, dept_avg
   FROM with_avg
   WHERE salary > dept_avg
   ORDER BY dept_id, salary DESC;

   Try it

   3 rows

   name	dept_id	salary	dept_avg
   Asha	1	95000	78750
   Hana	1	80000	78750
   Dara	2	60000	52500

Step 4 Interview-ready, then prove it

Your interview answer

About 20 to 40 seconds, said out loud.

I need each employee's department average next to their row, so I compute AVG of salary over a window partitioned by department in a CTE, and keep rows where salary exceeds it. An equivalent approach joins employees to a grouped subquery of averages per department. I'd mention the NULL-department case, since the two approaches treat it differently.

Follow-up questions

Tick each check when you can do it without notes