All sample questions / SQL

What does SELECT name, points, RANK() OVER (ORDER BY points DESC) AS rnk, ... return? SQL Window Functions

Block 6, Window Functions, question 9 of the SQL sample

CREATE TABLE contest2 (id INTEGER, name TEXT, points INTEGER);
INSERT INTO contest2 VALUES
  (1, 'Fay', 70),
  (2, 'Gus', 70),
  (3, 'Hana', 65),
  (4, 'Ivo', 60);


SELECT name, points,
       RANK() OVER (ORDER BY points DESC) AS rnk,
       DENSE_RANK() OVER (ORDER BY points DESC) AS drnk
FROM contest2
ORDER BY points DESC, name

What does this query return?

  1. A. Fay | 70 | 1 | 1 Gus | 70 | 1 | 1 Hana | 65 | 3 | 2 Ivo | 60 | 4 | 3
  2. B. Fay | 70 | 1 | 1 Gus | 70 | 1 | 1 Hana | 65 | 2 | 3 Ivo | 60 | 3 | 4
  3. C. Fay | 70 | 1 | 1 Gus | 70 | 2 | 2 Hana | 65 | 3 | 3 Ivo | 60 | 4 | 4
  4. D. Fay | 70 | 1 | 1 Gus | 70 | 1 | 1 Hana | 65 | 2 | 2 Ivo | 60 | 3 | 3
Show the answer and why each option is right or wrong

Answer, A. Fay | 70 | 1 | 1 Gus | 70 | 1 | 1 Hana | 65 | 3 | 2 Ivo | 60 | 4 | 3

A. Fay | 70 | 1 | 1 Gus | 70 | 1 | 1 Hana | 65 | 3 | 2 Ivo | 60 | 4 | 3
Correct. Both functions tie Fay and Gus at 1, then RANK jumps to 3 for Hana because two rows sit above it, while DENSE_RANK moves to 2 with no gap.
B. Fay | 70 | 1 | 1 Gus | 70 | 1 | 1 Hana | 65 | 2 | 3 Ivo | 60 | 3 | 4
This swaps the two columns for the non tied rows, giving RANK the gapless numbers that belong to DENSE_RANK and handing DENSE_RANK the skipped numbers that belong to RANK.
C. Fay | 70 | 1 | 1 Gus | 70 | 2 | 2 Hana | 65 | 3 | 3 Ivo | 60 | 4 | 4
This applies ROW_NUMBER logic to both columns, breaking the tie between Fay and Gus instead of letting RANK and DENSE_RANK agree on 1 for both of them.
D. Fay | 70 | 1 | 1 Gus | 70 | 1 | 1 Hana | 65 | 2 | 2 Ivo | 60 | 3 | 3
This applies DENSE_RANK logic to both columns, so RANK never skips ahead after the tie even though it counts rows above the current one rather than distinct values.

RANK and DENSE_RANK only disagree once a tie has happened, and only about what comes right after it. RANK counts rows above the current one, so a tie of size two is followed by a jump of two, while DENSE_RANK counts distinct values and always moves to the next integer.

The answer above is what SQLite and DuckDB both returned when the snippet was run, not what someone expected it to do.

This question comes from a bank of 502 SQL interview questions, all built the same way, sold as one PDF with the full answer key.

Get the 502 SQL questions, 9 euros

Or SQL, Python, pandas and NumPy together, 1,636 questions, 19 euros

Or play the free SQL sample, twenty questions in the browser, also as a free PDF.

Previous question Next question

Did this save you a lookup? Buy me a coffee on Ko-fi.