Skip to content
UniCircle
Menu
Sign inJoin
GuideEngineeringB.Tech / B.E. (CSE, IT)

Window functions for interviews: ROW_NUMBER, RANK and running totals

The three window-function patterns that solve most 'top N per group' and 'running total' questions in SQL interviews, with the difference between RANK and DENSE_RANK.

By UniCircle team2 min readPublished 22 Oct 2026Undergraduate

A window function computes a value for each row from a set of related rows, without collapsing them the way GROUP BY does. The set is described by OVER (PARTITION BY ... ORDER BY ...).

Numbering and ranking

SQL
CREATE TABLE scores (name text, branch text, marks int);
INSERT INTO scores VALUES
  ('A','CSE',95), ('B','CSE',95), ('C','CSE',90), ('D','ECE',88), ('E','ECE',80);
SELECT name, marks,
  ROW_NUMBER() OVER (ORDER BY marks DESC, name) AS rn,
  RANK()       OVER (ORDER BY marks DESC) AS rnk,
  DENSE_RANK() OVER (ORDER BY marks DESC) AS dense
FROM scores ORDER BY marks DESC, name;
text
A|95|1|1|1
B|95|2|1|1
C|90|3|3|2
D|88|4|4|3
E|80|5|5|4
  • ROW_NUMBER always counts 1, 2, 3, even on ties.
  • RANK gives ties the same number and then skips (1, 1, 3).
  • DENSE_RANK gives ties the same number without skipping (1, 1, 2).

Top student in each branch

SQL
CREATE TABLE scores (name text, branch text, marks int);
INSERT INTO scores VALUES ('A','CSE',95), ('C','CSE',90), ('D','ECE',88), ('E','ECE',80);
SELECT branch, name FROM (
  SELECT branch, name, ROW_NUMBER() OVER (PARTITION BY branch ORDER BY marks DESC) AS rn FROM scores
) t WHERE rn = 1 ORDER BY branch;
text
CSE|A
ECE|D

PARTITION BY restarts the numbering for every branch. You cannot filter on rn in the same SELECT, so wrap it in a subquery.

Running total

SQL
CREATE TABLE sales (day int, amount int);
INSERT INTO sales VALUES (1, 100), (2, 50), (3, 200);
SELECT day, amount, sum(amount) OVER (ORDER BY day) AS running FROM sales ORDER BY day;
text
1|100|100
2|50|150
3|200|350

Practise more in UniCircle interview prep: databases and SQL.

#sql#window functions#rank#interview

Sources and further reading

Written by the UniCircle team, checked by an editor on 22 Oct 2026. It is not official policy of any university. Exam patterns and rules change; confirm dates and rules on the official website.

Keep reading