RANK vs DENSE_RANK on Tied Scores — SQL

Solving tips

RANK leaves gaps after ties (1,1,3,...); DENSE_RANK does not (1,1,2,...) — pick based on whether skipped positions matter.
Ties are defined purely by the ORDER BY expression of the window; two rows tie only if those values are equal.
Neither function needs PARTITION BY unless you want the ranking to restart per group.

You have exam scores with several ties. The interviewer wants you to show, side by side, how RANK and DENSE_RANK number the tied rows differently.

Schema

CREATE TABLE exam_scores (
    student TEXT,
    score   INT
);

student	score
Ann	95
Bob	88
Cara	95
Dan	88
Eve	72
Finn	88

Task

Rank students by score from highest to lowest. Return student, score, the RANK value as rnk, and the DENSE_RANK value as dense_rnk. Order the output by score descending, then student ascending.

Expected output

student	score	rnk	dense_rnk
Ann	95	1	1
Cara	95	1	1
Bob	88	3	2
Dan	88	3	2
Finn	88	3	2
Eve	72	6	3

Approach

Both functions share the same ORDER BY score DESC window, so they agree on which rows tie. They differ only in what happens after a tie: RANK skips the positions the tied rows consumed, while DENSE_RANK keeps numbering consecutively. Computing both in one SELECT makes the gap-vs-no-gap behavior visible.

Query

SELECT
    student,
    score,
    RANK()       OVER (ORDER BY score DESC) AS rnk,
    DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rnk
FROM exam_scores
ORDER BY score DESC, student;

Walkthrough

The two 95 rows (Ann, Cara) tie for the top: both get rnk = 1 and dense_rnk = 1.
The three 88 rows come next. Two rows already occupied positions 1 and 2, so RANK resumes at 3 for all three. DENSE_RANK ignores the count and simply moves to the next distinct value, 2.
The lone 72 (Eve) follows. RANK has now passed five rows, so it is 6; DENSE_RANK gives it 3, the third distinct score.
The outer ORDER BY score DESC, student is independent of the window ordering and just fixes the display sequence and the alphabetical tie-break within equal scores.

Complexity & notes

Both windows use the identical ORDER BY, so the planner sorts once and computes both functions in a single pass — cost is one O(n log n) sort.
rank and row_number are reserved-ish words in some tools; aliasing to rnk / dense_rnk avoids quoting headaches.
NULL scores would sort together (all NULLs are “equal” for ranking) and land last under DESC with NULLS LAST semantics being dialect-dependent — add an explicit NULLS LAST if the data can contain them.
Use RANK when a skipped position is meaningful (e.g. “no 2nd place awarded because two tied for 1st”); use DENSE_RANK when you need a compact 1..k labeling of distinct tiers.