RANK, DENSE_RANK and intentional peers

sqlrelational-datasqlitepostgresqlindependent-practice

Saved on this device

Full lesson · 4 learning steps Open any exercise for focused practice

Step 1 of 4 · intro card

RANK, DENSE_RANK and intentional peers

Concept and contract

Preserve measured-value ties while choosing whether subsequent ranks leave gaps.

RANK and DENSE_RANK assign equal positions to peers under their window ordering. The worked query ranks known salary values globally, using salary alone to define peers. Roles 4 and 6 tie at 120 and both receive rank one under both functions.

After the first two-row peer group, RANK moves to three while DENSE_RANK moves to two. These numbers answer different ranking questions. Neither automatically represents a probability, a hiring recommendation or a measure of candidate suitability.

Mechanism and worked example

Peers are rows equal on every expression in the window ORDER BY.

Peers are rows equal on every expression in the window ORDER BY. The outer result orders by salary descending and id to display peers deterministically. Identity belongs only to that display order here; adding it inside the ranking window would intentionally break salary ties.

The expected rank sequence by displayed identity is 4 and 6 at one, 1 at rank three, 5 at four, 2 and 7 at five, and 8 at seven. Dense ranks for those salary bands are one through five without gaps.

Complete worked example

sql

SELECT id, RANK() OVER (ORDER BY salary DESC) AS position,
 DENSE_RANK() OVER (ORDER BY salary DESC) AS band
FROM roles WHERE salary IS NOT NULL ORDER BY salary DESC,id;

Reasoning: Salary defines peers; display identity does not alter their shared ranks.

Counterexamples and common mistakes

The counterexample adds id to the ranking order.

The counterexample adds id to the ranking order. Every identity is unique, so equal salaries cease to be peers and ranks become a simple sequence. This is correct for a different contract but wrong when the report promises equal measured values share a band.

Choose the tie policy before choosing the function. Including all tied top salaries can return more than a fixed N rows. Exactly N rows and top N distinct measured values are different requests, and tests need fixtures with ties to expose that difference.

Counterexample: a plausible but wrong query

sql

SELECT id, RANK() OVER (ORDER BY salary DESC,id) AS position,
 DENSE_RANK() OVER (ORDER BY salary DESC,id) AS band
FROM roles WHERE salary IS NOT NULL ORDER BY salary DESC,id;

Reasoning: A unique identity inside the window destroys salary peer groups.

Independent practice: small query

Return roles in the two highest distinct known salary bands using DENSE_RANK.

Return roles in the two highest distinct known salary bands using DENSE_RANK. Predict roles 4 and 6 in band one and role 1 in band two. Keep the outer display order deterministic without adding identity to the band-defining window.

Use a ranked intermediate result and filter its band column afterward. This changes the selection policy from the previous lesson's fixed row positions. Do not replace the band predicate with LIMIT 2, which would lose role 1 or one tied top record.

Step 1: Assign one dense band per known salary value. Step 2: Filter the completed band and sort the result.

Practice starter: solve before reading the solution

sql

-- Rank known salaries by distinct value, then return bands <=2.
-- Keep id only in the outer display tie-breaker.

Reasoning: Filter distinct value bands rather than limiting two source rows.

Hint 1: A salary tie must share the same band.
Hint 2: Do not add id inside the DENSE_RANK ordering.

Solution, tests and trade-offs

The solution returns three role identities across two distinct salary bands.

The solution returns three role identities across two distinct salary bands. The result size is deliberately larger than two because the highest band contains a tie. This is not a duplicate bug: each selected identity is a separate eligible source record.

Compare identity and band together in the acceptance check. A query returning only three arbitrary high records could have the same count but mislabel ties or omit the second distinct value. The known zero remains in the input population even though its band is below the selected cutoff.

Practice solution and acceptance checks

sql

WITH ranked AS (SELECT id,salary,DENSE_RANK() OVER (ORDER BY salary DESC) AS band
 FROM roles WHERE salary IS NOT NULL)
SELECT id,band FROM ranked WHERE band<=2 ORDER BY salary DESC,id;

Reasoning: Three records occupy the two highest distinct salary bands.

Interview reasoning and next step

In an interview, draw a short sequence with repeated values and calculate all three numbering functions by hand.

In an interview, draw a short sequence with repeated values and calculate all three numbering functions by hand. State whether ties should share a position and whether gaps matter. Then map that policy to ROW_NUMBER, RANK or DENSE_RANK.

The next lesson uses aggregate windows to attach group measures to each retained row. Keep the same distinction between a window's computation and the outer display order; a window is not a replacement for declaring the final result sequence.

Optional stretch — after the core task works: Compare the top-two-row result with top-two-distinct-value bands on the same fixture.

Computer practice: https://applydjinn.com/typorithm/sql-lab-20261105.zip. Load the shared setup.sql fixture before running a snippet. The local verifier compares exact ordered row arrays in SQLite and PostgreSQL, keeps SQL values separate from driver bindings and never connects to the production website database.

Preserve measured-value ties while choosing whether subsequent ranks leave gaps.