An e-commerce BI team shipped a "top 5 products by revenue" dashboard using RANK and discovered three months later it was showing 4 products on half the pages because two items kept tying at rank 3. The fix was one word: DENSE_RANK. Support tickets stopped the next morning.
DENSE_RANK shows up alongside RANK in 11% of verified SQL interview questions in our corpus. Interviewers love the Nth highest value prompt because the wrong ranking function gives you the wrong answer with zero error messages. This page covers syntax, the failure modes that hit production, and four interview questions pulled from phone screens.
RANK / DENSE_RANK questions
Months to detect the bug
Word to fix it
Error messages when wrong
Source: DataDriven analysis of 1,042 verified data engineering interview rounds.
Zero arguments. Mandatory OVER clause. ORDER BY is required or the result is undefined. PARTITION BY is optional, but skip it once in a top-N-per-group query and you'll spend an hour figuring out why rank 1 only appears in one department. Ask anyone who's written a Looker tile at 11pm.
-- 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;DENSE_RANK works in PostgreSQL, MySQL 8+, SQL Server, Oracle, Snowflake, BigQuery, Redshift, and every other modern SQL engine. The syntax is identical across all of them. No vendor-specific variations exist for this function.
The three ranking functions agree on non-tied rows. They only diverge when duplicates appear in the ORDER BY column. This table shows salaries with two pairs of ties so you can see the divergence clearly.
Same data, three functions
| Salary | ROW_NUMBER | RANK | DENSE_RANK |
|---|---|---|---|
| $120,000 | 1 | 1 | 1 |
| $110,000 | 2 | 2 | 2 |
| $110,000 | 3 | 2 | 2 |
| $100,000 | 4 | 4 | 3 |
| $95,000 | 5 | 5 | 4 |
| $95,000 | 6 | 5 | 4 |
| $90,000 | 7 | 7 | 5 |
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.
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;DENSE_RANK shines in three specific scenarios. In all other cases, ROW_NUMBER (for uniqueness) or RANK (for competition-style gaps) is usually a better fit.
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. RANK would also assign rank 2 here, but for larger N values, the gap behavior becomes problematic. ROW_NUMBER would assign different ranks to the tied rows, potentially returning the wrong salary.
-- 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)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. This distinction between 'top N values' and 'top N rows' is a frequent interview trap.
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 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. This is useful for pricing tiers, performance bands, or any classification where the tier number itself carries meaning and gaps would confuse downstream consumers.
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 priceNULLs are treated as equal to each other for ranking purposes. All NULL values in the ORDER BY column receive the same DENSE_RANK. Where NULLs land depends on the engine and sort direction. PostgreSQL defaults to NULLS LAST for ASC and NULLS FIRST for DESC. SQL Server places NULLs first for ASC.
-- 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.
These questions specifically test DENSE_RANK knowledge. Each includes what the interviewer is looking for and the recommended approach.
What they test:
This is the DENSE_RANK signature question. The interviewer specifically mentions ties to see if you reach for DENSE_RANK instead of ROW_NUMBER. ROW_NUMBER would give different ranks to tied employees and might return the wrong salary as 'second highest.' RANK would work for this specific case but DENSE_RANK is the cleaner choice.
Approach:
DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dr, then filter WHERE dr = 2. If the question asks for the employee rows, keep all rows with dr = 2. If it asks for the salary value, add DISTINCT.
What they test:
Conceptual clarity. The interviewer wants to hear about gaps. RANK leaves a gap after tied rows; DENSE_RANK does not. The example should have at least one tie so the difference is visible. A strong answer also explains when the gap matters (filtering by rank number) vs when it does not (just displaying the rank).
Approach:
Draw the example: values 100, 90, 90, 80. RANK: 1, 2, 2, 4. DENSE_RANK: 1, 2, 2, 3. The gap in RANK means WHERE rnk = 3 returns nothing. WHERE dr = 3 with DENSE_RANK returns 80. Use DENSE_RANK when you need to address specific rank positions by number.
What they test:
This tests whether you recognize that MAX(DENSE_RANK()) gives the count of distinct values. Since DENSE_RANK produces contiguous integers starting at 1, the maximum rank equals the number of distinct values. This is an alternative to COUNT(DISTINCT price) that works in contexts where COUNT DISTINCT is not available (like some window function scenarios).
Approach:
Two approaches. Simple: COUNT(DISTINCT price) with GROUP BY category. Window function approach: DENSE_RANK() OVER (PARTITION BY category ORDER BY price) and then take MAX per category. The second approach demonstrates DENSE_RANK understanding, which is what the interviewer wants to see.
What they test:
The phrase 'no rank numbers should be skipped' is the direct signal for DENSE_RANK. If a candidate uses RANK instead, the output will have gaps after ties, violating the requirement. This tests whether you read the question carefully and pick the right function.
Approach:
DENSE_RANK() OVER (PARTITION BY class_id ORDER BY test_score DESC) AS student_rank. The 'no skipped numbers' requirement eliminates RANK. The 'same score same rank' requirement eliminates ROW_NUMBER. Only DENSE_RANK satisfies both.
A quick decision guide for the interview whiteboard:
Need exactly one row per group?
Use ROW_NUMBER(). It guarantees uniqueness. Ties are broken arbitrarily (or by a secondary ORDER BY column you specify).
Ties should share a rank, and gaps are acceptable?
Use RANK(). Two rows at 2nd place means 3rd place does not exist. Like a sports podium.
Ties should share a rank, and rank numbers must be contiguous?
Use DENSE_RANK(). No gaps. The Nth rank always exists if there are at least N distinct values.
Pick the wrong function and the query runs fine, returns fewer rows than the interviewer expected, and nobody catches it for weeks. Drill the three functions until the choice is reflex.