ROW_NUMBER vs RANK vs DENSE_RANK

The three functions behave identically until two rows tie. Then they disagree, and the tie is what interview questions are testing.

SELECT name, 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
FROM employees
WHERE salary IS NOT NULL
ORDER BY salary DESC
LIMIT 8;

Pavel Novak and Meera Pillai both earn 131,000.

• ROW_NUMBER gives them different numbers anyway. Which one gets the lower number is arbitrary unless you add a tie-breaker to ORDER BY, and it can change between runs.
• RANK gives them the same number and then skips: 3, 3, 5. The next person is fifth because four people earn more.
• DENSE_RANK gives them the same number and doesn't skip: 3, 3, 4. The next person has the fourth-highest distinct salary.

Which one to use

"The second-highest salary" usually means the second-highest distinct value, so use DENSE_RANK. Salaries tying at the top won't push the answer down a place.

WITH ranked AS (
  SELECT name, salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS d
  FROM employees
  WHERE salary IS NOT NULL
)
SELECT name, salary FROM ranked WHERE d = 3;

Both people on the third-highest salary come back. Using ROW_NUMBER with = 3 would have returned one of them and quietly dropped the other.

"Exactly one row per group", such as each customer's latest order, needs ROW_NUMBER, since you want one row even if two orders share a date. Add a tie-breaker so the result is repeatable.

"Competition placing", where two people tie for second and nobody is third, is RANK.

Common follow-up

Why not put the window function directly in WHERE? Window functions are computed after WHERE has run, so they aren't available there. That's the reason for the CTE or subquery wrapper in the example.