Which Queries Get Asked Every Time?

This is the classic per-group extremes question, and the window-function version answers it without collapsing the group or self-joining the table.

CREATE TABLE employees (
  id INTEGER PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  department_id INTEGER NOT NULL,
  salary_cents INTEGER NOT NULL
);

INSERT INTO employees (id, name, department_id, salary_cents) VALUES
  (1, 'Ada Lovelace', 1, 120000),
  (2, 'Grace Hopper', 1, 150000),
  (3, 'Alan Turing',  2,  95000),
  (4, 'Joan Clarke',  2,  87000),
  (5, 'Katherine Johnson', 2, 87000);

SELECT department_id,
       name,
       salary_cents,
       MAX(salary_cents) OVER (PARTITION BY department_id) AS dept_max,
       MIN(salary_cents) OVER (PARTITION BY department_id) AS dept_min
FROM employees
ORDER BY department_id, salary_cents DESC;

Five rows come back. Department 1 shows Grace at 150000 and Ada at 120000, each carrying the department maximum of 150000 and minimum of 120000. Department 2 shows Alan at 95000, then Joan and Katherine both at 87000, each carrying the maximum of 95000 and the minimum of 87000.

The window version annotates every employee rather than picking out the extremes. That is often what a report wants, because it lets you rank, and it also handles the tie in department 2 correctly: both Joan and Katherine are shown as holding the minimum.

If you want only the extreme rows, filter on the computed value in an outer query — WHERE salary_cents = dept_max for the highest. The self-join version is the traditional answer and works on any engine, including ones without window functions: join the table to itself on the department and filter for rows where no other employee in the same department has a higher salary. It is worth being able to write, because it is what a lot of interview answers use.

One detail that catches people: the MIN in department 2 is 87000 and two employees hold it. Any query that assumes a single lowest-paid employee per department is wrong as soon as there is a tie, and the tie is exactly what the interview is testing.

The self-join version is the answer that has been asked in interviews for twenty years, and it is worth being able to produce on demand.

CREATE TABLE employees (
  id INTEGER PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  department_id INTEGER NOT NULL,
  salary_cents INTEGER NOT NULL
);

INSERT INTO employees (id, name, department_id, salary_cents) VALUES
  (1, 'Ada Lovelace', 1, 120000),
  (2, 'Grace Hopper', 1, 150000),
  (3, 'Alan Turing',  2,  95000),
  (4, 'Joan Clarke',  2,  87000),
  (5, 'Katherine Johnson', 2, 87000);

SELECT e.name,
       e.department_id,
       e.salary_cents
FROM employees AS e
WHERE (
  SELECT COUNT(*)
  FROM employees AS e2
  WHERE e2.department_id = e.department_id
    AND e2.salary_cents > e.salary_cents
) = 1
ORDER BY e.department_id, e.name;

Three rows come back: Ada Lovelace in department 1 at 120000, and Joan Clarke and Katherine Johnson in department 2 at 87000.

Read the correlated subquery. For each employee, it counts how many colleagues in the same department earn strictly more. If that count is exactly one, then exactly one person in the department earns more than this employee — which makes this employee the second highest.

Notice what happens with the tie in department 2. Joan and Katherine both earn 87000, and only Alan earns more than either of them, so both have a count of one and both are returned. That is correct for the second-highest distinct salary, and it is the behaviour the self-join gives you for free. Contrast it with the LIMIT and OFFSET version, which returns the second row rather than the second distinct value and would give a different answer.

Two other ways to write it, both worth knowing. A window function version — WHERE DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary_cents DESC) = 2 inside a subquery — is shorter and clearer. And the correlated MAX version, WHERE e.salary_cents = (SELECT MAX(salary_cents) FROM employees e3 WHERE e3.department_id = e.department_id AND e3.salary_cents < e.salary_cents), computes the second-highest distinct value directly rather than counting competitors.