CodePark/ questions / SQL queries & joins
START PRACTICINGPRACTICE
Backend question · Databases & SQL › SQL queries & joins

Explain how these two queries work. How would the top-3 result change if you used ROW_NUMBER or RANK instead of DENSE_RANK and salaries tie?

-- second highest distinct salary
SELECT MAX(salary) FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

-- top 3 salaries per department
WITH ranked AS (
  SELECT e.*, DENSE_RANK() OVER (
    PARTITION BY dept_id ORDER BY salary DESC
  ) AS rnk
  FROM employees e
)
SELECT * FROM ranked WHERE rnk <= 3;

The first query finds the second highest distinct salary. The second query finds the top 3 salaries in each department.

First query: it finds the highest salary that is below the maximum. That is the second highest distinct value. It returns NULL when there is only one distinct salary. Another way to write it is SELECT DISTINCT salary ... ORDER BY salary DESC LIMIT 1 OFFSET 1.

Second query: it uses a window function. A window function computes a value for each row from a group of related rows, without merging the rows.

  • PARTITION BY dept_id restarts the ranking for each department.
  • ORDER BY salary DESC ranks the rows inside each department.
  • Window functions run after WHERE. So you cannot filter on rnk in the same query. You need the CTE (a named subquery written with WITH) or a subquery.

With ties at the top, say salaries 100, 100, 90:

  • ROW_NUMBER gives 1, 2, 3. It breaks ties in any order and returns exactly 3 rows.
  • RANK gives 1, 1, 3. It leaves gaps after ties.
  • DENSE_RANK gives 1, 1, 2. It leaves no gaps, so you get the top 3 distinct salaries.
  • Find the second highest salary with MAX below MAX, or with DISTINCT and OFFSET 1.
  • PARTITION BY restarts the ranking for each group.
  • You must filter window results in an outer query or a CTE.
  • ROW_NUMBER is always unique, RANK leaves gaps, and DENSE_RANK leaves no gaps.