SQL DENSE_RANK Explained

-- Basic syntax
DENSE_RANK() OVER (
  [PARTITION BY partition_column, ...]
  ORDER BY sort_column [ASC|DESC]
)

-- Example: rank employees by salary within each department
SELECT
  department,
  employee_name,
  salary,
  DENSE_RANK() OVER (
    PARTITION BY department
    ORDER BY salary DESC
  ) AS salary_rank
FROM employees;

SELECT
  salary,
  ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,
  RANK()       OVER (ORDER BY salary DESC) AS rnk,
  DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rnk
FROM employees
ORDER BY salary DESC;

Key insight: DENSE_RANK produces 5 distinct values for 7 rows. RANK also has 5 distinct values but skips 3 and 6. ROW_NUMBER has 7 unique values with no ties at all. The maximum DENSE_RANK value always equals the count of distinct ORDER BY values.

-- Find the 3rd highest salary
SELECT DISTINCT salary
FROM (
  SELECT
    salary,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS dr
  FROM employees
) ranked
WHERE dr = 3;

-- If salaries are 120k, 110k, 110k, 100k, 95k
-- DENSE_RANK: 1, 2, 2, 3, 4 => dr=3 returns 100k (correct)
-- RANK:       1, 2, 2, 4, 5 => dr=3 returns nothing (gap)
-- ROW_NUMBER: 1, 2, 3, 4, 5 => dr=3 returns 110k (wrong)

The classic interview question: find the 2nd (or Nth) highest salary. DENSE_RANK is the correct choice because it counts distinct values sequentially. If two employees share the top salary, the next distinct salary gets rank 2, not rank 3.

WITH ranked AS (
  SELECT
    department,
    salary,
    DENSE_RANK() OVER (
      PARTITION BY department
      ORDER BY salary DESC
    ) AS dr
  FROM employees
)
SELECT DISTINCT department, salary, dr
FROM ranked
WHERE dr <= 3
ORDER BY department, dr;

When the question asks for the top 3 salaries in each department (not the top 3 employees), DENSE_RANK handles duplicates gracefully. Two employees earning the same salary both get the same rank, and the third distinct salary gets rank 3 regardless of how many people share ranks 1 and 2.

SELECT
  product_id,
  product_name,
  unit_price,
  DENSE_RANK() OVER (ORDER BY unit_price DESC) AS price_tier
FROM products;

-- Tier 1: most expensive
-- Tier 2: second most expensive
-- No gaps even if multiple products share a price

When you need to assign items to numbered tiers based on a metric and want the tier numbers to be contiguous (1, 2, 3, not 1, 2, 5), DENSE_RANK provides that guarantee.

-- PostgreSQL: NULLs sorted to the end for ASC
SELECT
  employee_name,
  bonus,
  DENSE_RANK() OVER (ORDER BY bonus DESC NULLS LAST) AS bonus_rank
FROM employees;

-- Bonuses: 5000, 3000, 3000, NULL, NULL
-- Ranks:   1,    2,    2,    3,    3
-- Both NULLs share rank 3 (treated as equal)

Interview note: If the interviewer asks 'what happens to NULLs in DENSE_RANK,' the correct answer is that all NULLs share one rank. Mention NULLS FIRST/LAST to control placement. Filtering NULLs out before ranking is often the safest approach if NULLs should not participate in the ranking at all.