What does COUNT(DISTINCT h.salary) + 1 compute in a self-join with ON h.salary > e.salary?

## Ranking expressed as counting A rank is just a count: your position in a descending list is the number of things ahead of you, plus one. A self-join makes "the things ahead of you" concrete — join the table to itself on a **non-equi** predicate that means "strictly better", and count the matches per row. ```sql SELECT e.employee_id, e.name, e.salary, COUNT(DISTINCT h.salary) + 1 AS salary_rank FROM employees AS e LEFT JOIN employees AS h ON h.salary > e.salary GROUP BY e.employee_id, e.name, e.salary ORDER BY salary_rank; ``` This is the classic pre-window-function formulation of ranking, and it is still asked because it tests whether you understand joins as row pairing rather than as table lookup. ## Why LEFT JOIN, not JOIN The highest-paid employee has nobody earning more, so the predicate `h.salary > e.salary` matches nothing for that row. An inner join therefore drops exactly the row you most wanted — rank 1 is missing from a ranking query, which is a memorable bug. `LEFT JOIN` preserves `e` and NULL-extends all `h` columns. ## Why COUNT(DISTINCT ...) and not COUNT(*) Two separate issues hide in the choice of counting expression. First, NULLs. Under `LEFT JOIN`, the unmatched top earner still contributes **one** row to its group, with every `h` column NULL. `COUNT(*)` counts rows, so it returns 1 and the top earner ranks 2. `COUNT(h.employee_id)` and `COUNT(DISTINCT h.salary)` ignore NULLs and correctly return 0. Getting this wrong shifts the entire ranking by one for exactly one row, which is easy to miss in a spot check. Second, ties. Counting distinct higher **salaries** produces a dense ranking: three people on 100, 100 and 90 rank 1, 1 and 2. Counting matched **rows** produces a competition ranking: 1, 1 and 3, because the person on 90 has two people above them. Neither is wrong — they answer different questions — but you must say which one the requirement wants: ```sql -- competition ranking: ties consume the numbers below them COUNT(h.employee_id) + 1 AS salary_rank ``` ## Selecting the nth highest Because the rank is an aggregate, `HAVING` filters it. The nth-highest distinct salary is the one with exactly n-1 distinct salaries above it: ```sql SELECT e.salary FROM employees AS e JOIN employees AS h ON h.salary > e.salary GROUP BY e.salary HAVING COUNT(DISTINCT h.salary) = 1; -- second-highest distinct salary ``` Here the inner join is fine because n-1 is at least 1, so a match is guaranteed. Grouping by `e.salary` rather than by employee returns the salary once, however many people are on it; grouping by employee returns every person at that rank. ## Per-group ranking Add the partitioning column as an equality alongside the inequality — this is the shape of every "rank within department" question: ```sql SELECT e.employee_id, e.department_id, COUNT(DISTINCT h.salary) + 1 AS dept_rank FROM employees AS e LEFT JOIN employees AS h ON h.department_id = e.department_id AND h.salary > e.salary GROUP BY e.employee_id, e.department_id; ``` The equality confines the comparison to the employee's own department; forgetting it silently ranks everyone against the whole company. ## Reading the shape of the output Before aggregation this join emits, for each employee, one row per better-paid employee — roughly half of all ordered pairs across the table. That is quadratic in the row count by construction, which is why this formulation is a teaching and small-data tool rather than the thing you run over millions of rows. It is worth saying out loud in an interview, along with the modern alternative: `DENSE_RANK() OVER (ORDER BY salary DESC)` expresses the same result directly on engines with window-function support, which entered the standard in SQL:2003. Knowing both, and knowing they agree, is the point. ## What separates a good answer Stating that a rank is a count of better rows; naming LEFT JOIN and *why*; distinguishing `COUNT(DISTINCT salary)` from `COUNT(rows)` as dense versus competition ranking; spotting that `COUNT(*)` is broken here specifically because of the NULL-extended row; and knowing how to pin the nth value with `HAVING`.