A window function calculates a value over related rows while leaving those rows in the result. It differs from grouping, which combines rows into groups. Ranking questions also need a clear tie policy and an ordering that supports the intended answer.

The examples use SQL Server and fictional product scores. They demonstrate output rules, not real sales performance.

How do ROW_NUMBER and RANK handle ties?

DECLARE @Scores TABLE (ProductId int PRIMARY KEY, Region varchar(10), Score int);
INSERT @Scores VALUES
 (1, 'north', 90), (2, 'north', 90), (3, 'north', 70),
 (4, 'south', 80);

SELECT ProductId, Region, Score,
 ROW_NUMBER() OVER (PARTITION BY Region ORDER BY Score DESC, ProductId) AS RowPosition,
 RANK() OVER (PARTITION BY Region ORDER BY Score DESC) AS SharedRank,
 DENSE_RANK() OVER (PARTITION BY Region ORDER BY Score DESC) AS DenseRank
FROM @Scores
ORDER BY Region, RowPosition;

Within north, RowPosition is 1,2,3 because ProductId breaks the equal-score tie. SharedRank is 1,1,3, while DenseRank is 1,1,2. South starts a separate partition and receives rank 1.

Why does the tie-breaker differ between the expressions?

The unique ProductId makes row numbering predictable for these rows. The rank expressions intentionally order only by Score so equal scores remain ties. Adding ProductId there would change the definition of an equal ordering value and remove the shared rank in this example.

Do not add a tie-breaker mechanically to every window. First decide whether the question needs a stable unique position or a shared business rank.

Does window ORDER BY sort the final result?

It defines the ordering used for that window calculation. A final ORDER BY controls presentation order for the result query. These can serve different purposes, so explicitly order the final result when a displayed sequence matters.

Likewise, an ordering that is not unique may not define which equal row receives a particular row number. Avoid claiming deterministic selection while leaving the relevant tie unresolved.

How is a running total different from a group total?

-- Continue in the same batch.
SELECT ProductId, Region, Score,
 SUM(Score) OVER (
   PARTITION BY Region ORDER BY ProductId
   ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
 ) AS RunningScore
FROM @Scores
ORDER BY Region, ProductId;

North's running values are 90,180,250. South's value is 80. The explicit row frame states which preceding rows participate. Do not assume every default frame behaves like a row-by-row running total when equal ordering values are present.

How do you select a top result per group?

Calculate the window result in a suitable subquery or common table expression, then filter the resulting value in the outer query. Decide whether “top” includes tied scores. A rank filter and a row-number filter can correctly return different row counts.

The original data grain stays important. If a join duplicated input rows before the window runs, the ranking can be wrong for the intended task even when the window syntax is valid.

Questions to practise

  • Which rows belong in each partition?
  • Should ties share a rank or be broken?
  • What ordering makes a selected row reproducible?
  • Which rows belong to the aggregate frame?

For the next exercise, add another south product with score 80. Predict all three ranking columns before running the query. Then change the requirement from “best row” to “all highest-scoring products” and explain why the filter choice changes.

References and next practice

learn.microsoft.com — sql window

sql question collection · Browse interview topics · Practise a question in Interview Challenge · How to Answer Technical Interview Questions Out Loud