SQL Quiz: Ranking Functions
10 questions · exam conditions
0:00
Ranking FunctionsQuestion 1 of 10

An Orders table contains these rows: for customer C1, order O1 has amount 200 and date 2026-06-02, order O2 has amount 200 and date 2026-06-01, and order O3 has amount 150 and date 2026-06-03. For customer C2, order O4 has amount 300 and date 2026-06-01, and order O5 has amount 100 and date 2026-06-02.

The query assigns ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY amount DESC, order_date ASC) AS rn. What row numbers are assigned to O1 and O5, respectively?

O1 receives 1, and O5 receives 2
O1 receives 2, and O5 receives 5
O1 receives 2, and O5 receives 2
O1 receives 1, and O5 receives 1
← Back to quizzes

SQL Quiz

SQL Quiz: Ranking Functions

Practice Ranking Functions in SQL with focused quiz questions that help you check what you know, review explanations, and build confidence with test-style prompts.

What this quiz covers

This quiz focuses on Ranking Functions, giving you a quick way to practice the rules, question types, and explanations that matter most for SQL.

How to use this quiz

Try each quiz question before looking at the correct answer. Use the explanations to review missed ideas, then come back to similar questions until the pattern feels familiar.

All questions

Question 1

An Orders table contains these rows: for customer C1, order O1 has amount 200 and date 2026-06-02, order O2 has amount 200 and date 2026-06-01, and order O3 has amount 150 and date 2026-06-03. For customer C2, order O4 has amount 300 and date 2026-06-01, and order O5 has amount 100 and date 2026-06-02.

The query assigns ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY amount DESC, order_date ASC) AS rn. What row numbers are assigned to O1 and O5, respectively?

  1. O1 receives 1, and O5 receives 2
  2. O1 receives 2, and O5 receives 5
  3. O1 receives 2, and O5 receives 2 (correct answer)
  4. O1 receives 1, and O5 receives 1
Explanation: When you see ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...), remember two things: the window function resets its counter for each partition, and it assigns strictly unique sequential integers based on the specified sort order. Here, the data is partitioned by customer_id, so C1 and C2 are ranked independently. Within each partition, rows are sorted by amount DESC first, then order_date ASC as a tiebreaker. For C1 (orders O1, O2, O3): sorting by amount descending gives O1 (200) and O2 (200) tied, then O3 (150). The tiebreaker order_date ASC puts O2 (June 1) before O1 (June 2). So the ranking is: O2 → rn=1, O1 → rn=2, O3 → rn=3. For C2 (orders O4, O5): O4 has amount 300, O5 has amount 100. So O4 → rn=1, O5 → rn=2. This confirms C: O1 receives 2 and O5 receives 2. Answer A is wrong because it assumes O1 is the top-ranked row in C1's partition, ignoring that O2 ties O1 on amount but sorts earlier by date. Answer B is wrong on both counts — O5 receiving 5 would only make sense if row numbering continued globally across partitions, which it doesn't. Answer D incorrectly gives O5 a rank of 1, which belongs to O4 since 300 > 100. The key study tip: always trace the ORDER BY clause step by step, including tiebreakers — they frequently determine whether a row ranks first or second, and exam questions are designed around exactly those edge cases.

Question 2

A query assigns ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) to employees. Several employees in the same department can have identical salaries, and no other ordering column is specified.

Which change is sufficient to guarantee stable row-number assignments for unchanged data, assuming employee_id is unique within each department?

  1. Add employee_id to the window ordering after salary DESC (correct answer)
  2. Replace ROW_NUMBER() with RANK() while keeping the same ordering
  3. Add an outer ORDER BY department_id, salary DESC to the query
  4. Replace PARTITION BY department_id with an outer grouping operation
Explanation: When working with window functions, the key concept to understand is determinism — whether a function produces the same result every time for the same data. ROW_NUMBER() assigns a unique integer to each row, but when tied rows exist and the ordering doesn't fully distinguish them, the database engine can arbitrarily break those ties, producing different orderings across executions. The fix is to make the ORDER BY clause within the window fully deterministic. Since employee_id is unique within each department, appending it after salary DESC gives the engine a tiebreaker that always resolves the same way. Every row gets a predictable, stable rank — which is exactly what A accomplishes. The ordering becomes ORDER BY salary DESC, employee_id, and ties in salary no longer produce ambiguous numbering. B is wrong because RANK() doesn't solve the problem — it actually moves in the opposite direction. RANK() assigns the same number to tied rows, so it sidesteps the uniqueness issue but doesn't guarantee stable numbering; it still uses the same non-deterministic ordering clause. C is a common trap. Adding ORDER BY department_id, salary DESC to the outer query controls the display order of results but has no effect on how the window function internally assigns row numbers. Window ordering and query-level ordering are independent. D misunderstands the purpose of PARTITION BY. Replacing it with a GROUP BY aggregation would collapse rows, destroying the row-level detail that ROW_NUMBER() is designed to annotate. Study tip: Whenever you see ROW_NUMBER() and the word "stable" or "deterministic," immediately check whether the window ORDER BY includes a unique column — that's the only guaranteed fix.

Question 3

Four attempts are ranked using RANK() OVER (ORDER BY score DESC, completion_seconds ASC). Attempt A has score 90 and time 30; attempts B and D each have score 90 and time 25; attempt C has score 85 and time 20.

What ranks are assigned to attempts A and C, respectively?

  1. A receives rank 2, and C receives rank 3
  2. A receives rank 3, and C receives rank 3
  3. A receives rank 2, and C receives rank 4
  4. A receives rank 3, and C receives rank 4 (correct answer)
Explanation: When working with RANK(), two rules govern everything: ties receive the same rank, and the next rank skips a number equal to the count of tied rows. The ORDER BY score DESC, completion_seconds ASC means higher scores rank better, and among equal scores, faster times rank better. Let's sort the four attempts: B and D both have score 90 and time 25 — they're the fastest among the 90s, so they tie for rank 1. A has score 90 but time 30 (slower), placing it next. Because two rows already occupy positions 1 and 2, A receives rank 3. Finally, C has a lower score of 85, so it falls after all the 90s. Since rank 3 was just used (only one row, A), the next rank skips to 4 — giving C rank 4. That confirms D as the correct answer. Choice A incorrectly gives A rank 2, ignoring that B and D both occupy the top positions, pushing A to rank 3. Choice B gets A's rank right at 3 but assigns C rank 3 as well — C cannot share rank 3 with A because C has a lower score, making it strictly worse. Choice C gives A rank 2 (same error as A) but correctly identifies C at rank 4. A good study tip: always count how many rows are tied above a given row to determine where RANK() lands. Since two rows tie at rank 1, the next assigned rank is 1 + 2 = 3, not 2 — RANK() always gaps, while DENSE_RANK() would not.

Question 4

For a row in one partition, a query ordered by metric DESC reports RANK() = 7 and DENSE_RANK() = 4. No other information about the partition is available.

What can be concluded with certainty about rows preceding this row in the window order?

  1. Exactly six rows precede it, representing exactly three distinct higher metric values (correct answer)
  2. Exactly seven rows precede it, representing exactly four distinct higher metric values
  3. Exactly six rows precede it, representing exactly four distinct higher metric values
  4. Exactly three rows precede it, representing exactly six distinct higher metric values
Explanation: When working with window functions, understanding the precise definitions of RANK() and DENSE_RANK() is essential. RANK() tells you how many rows have a strictly higher value than the current row, plus one. DENSE_RANK() tells you how many distinct values are strictly higher, plus one. So when you see RANK() = 7 and DENSE_RANK() = 4, you can extract concrete information. Since RANK() = 7, there are exactly 6 rows preceding this row with a higher metric (7 − 1 = 6). Since DENSE_RANK() = 4, there are exactly 3 distinct metric values among those preceding rows (4 − 1 = 3). This perfectly matches answer A: six rows precede it, representing three distinct higher metric values. This also logically implies ties exist among those 6 rows — 6 rows collapsed into only 3 distinct values. Answer B is wrong on both counts — it claims 7 preceding rows and 4 distinct values, which would correspond to RANK() = 8 and DENSE_RANK() = 5. Answer C gets the row count right (6) but misidentifies the distinct value count as 4, confusing DENSE_RANK() = 4 with the number of distinct values rather than subtracting 1. Answer D inverts the two values entirely, swapping the roles of RANK() and DENSE_RANK() — a classic trap when students haven't fully internalized what each function measures. A reliable memory trick: both functions use a "plus one" convention, so always subtract 1 from each to get the raw counts. RANK() − 1 = preceding rows; DENSE_RANK() − 1 = distinct preceding values.

Question 5

A query runs SELECT employee_id, salary, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn FROM Employees; and does not include an outer ORDER BY clause.

Which statement correctly describes what the query guarantees?

  1. Rows are returned in descending salary order because the window ordering also orders the final result
  2. The row numbers follow descending salary order, but the final display order is not guaranteed (correct answer)
  3. Both row-number assignment and final display order are arbitrary because no outer ordering exists
  4. Rows are returned in row-number order only when every salary value is distinct
Explanation: When working with window functions in SQL, it's essential to separate two distinct concepts: how values are computed versus how rows are physically returned. These are independent operations, and confusing them is one of the most common mistakes on SQL exams. The OVER (ORDER BY salary DESC) clause tells SQL how to rank or number rows within the window calculation — it determines which row gets rn = 1, which gets rn = 2, and so on. However, this ordering instruction lives entirely inside the window function and has no effect on the final result set's display order. Without an explicit outer ORDER BY clause at the query level, the database engine is free to return rows in any physical order it chooses — typically whatever is most efficient for execution. This makes B correct: the row numbers are meaningful and accurately reflect descending salary order, but the sequence in which those rows appear on your screen is not guaranteed. A is wrong because it conflates window ordering with result-set ordering — these are separate mechanisms, and one does not imply the other. C goes too far in the other direction: the row numbers are not arbitrary. They are deterministically assigned based on salary, so calling the row-number assignment arbitrary is incorrect. D introduces a false condition — distinct salary values are relevant only if you're considering ties with RANK() or DENSE_RANK(), not the physical display order guarantee, which remains undefined regardless of uniqueness. A practical tip: whenever you see OVER (ORDER BY ...), ask yourself whether there is also a query-level ORDER BY. If not, treat the result order as unpredictable, even if the window values look sorted.

Question 6

Within one department, employee salaries are 120000, 120000, 110000, and 100000. A report must return every employee whose salary is in the two highest distinct salary levels.

Which ranking expression and filter reliably satisfy the requirement, including the 110000 employee despite the tie at the highest salary?

  1. Calculate ROW_NUMBER() OVER (ORDER BY salary DESC) and filter for values at most 2
  2. Calculate RANK() OVER (ORDER BY salary DESC) and filter for values at most 2
  3. Calculate DENSE_RANK() OVER (ORDER BY salary DESC) and filter for values at most 2 (correct answer)
  4. Calculate ROW_NUMBER() OVER (ORDER BY salary ASC) and filter for values at most 2
Explanation: When SQL questions ask about "distinct salary levels" or "top N tiers," your first instinct should be to think about how ties are handled — because that's almost always the trap. The three window functions behave very differently with ties. ROW_NUMBER() assigns a unique sequential number to every row, breaking ties arbitrarily. RANK() gives tied rows the same rank but then skips the next rank (two employees tied at rank 1 produce no rank 2 — the next value jumps to rank 3). DENSE_RANK() gives tied rows the same rank and never skips — the next distinct value always gets the next consecutive rank. Given salaries of 120000, 120000, 110000, and 100000, DENSE_RANK() ORDER BY salary DESC assigns rank 1 to both 120000 earners, rank 2 to the 110000 earner, and rank 3 to the 100000 earner. Filtering for dense_rank <= 2 correctly captures all three employees in the top two salary levels — exactly what the requirement asks for. C is correct. A fails because ROW_NUMBER() assigns unique values 1, 2, 3, 4. Filtering for <= 2 returns only two employees, arbitrarily excluding one 120000 earner and the 110000 earner entirely. B uses RANK(), which assigns rank 1, 1, 3, 4. Filtering for <= 2 returns only the two 120000 employees — the 110000 earner sits at rank 3 and is excluded, missing the second distinct salary level. D orders salaries ascending, so the lowest salaries get the smallest ranks — this returns the two cheapest employees, the opposite of what's needed. Your study tip: memorize the trio as ROW_NUMBER = unique, RANK = gaps, DENSE_RANK = no gaps. Any question mentioning "distinct levels" or "tiers" is almost always signaling DENSE_RANK().

Question 7

A report must return at most three sales rows per region, choosing the rows with the greatest sale_amount. Ties may be broken by sale_id, so exactly three rows should be returned from each region that has at least three rows.

Which query structure correctly implements the requirement in standard SQL processing order?

  1. SELECT region, sale_id, sale_amount FROM Sales WHERE ROW_NUMBER() OVER (PARTITION BY region ORDER BY sale_amount DESC, sale_id) <= 3
  2. SELECT region, sale_id, sale_amount FROM Sales GROUP BY region, sale_id, sale_amount HAVING ROW_NUMBER() OVER (PARTITION BY region ORDER BY sale_amount DESC, sale_id) <= 3
  3. SELECT region, sale_id, sale_amount FROM (SELECT region, sale_id, sale_amount, ROW_NUMBER() OVER (PARTITION BY region ORDER BY sale_amount DESC, sale_id) AS rn FROM Sales) s WHERE rn <= 3 (correct answer)
  4. SELECT region, sale_id, sale_amount FROM (SELECT region, sale_id, sale_amount, RANK() OVER (PARTITION BY region ORDER BY sale_amount DESC) AS rn FROM Sales) s WHERE rn <= 3
Explanation: Whenever you see a question about window functions like ROW_NUMBER(), the critical concept to remember is SQL's logical processing order: FROM → WHERE → GROUP BY → HAVING → SELECT. Window functions are evaluated during the SELECT phase, which means you cannot reference them in WHERE or HAVING clauses of the same query level — they don't exist yet at those stages. Option C is correct because it wraps the window function inside a subquery (the inner FROM clause), fully resolving ROW_NUMBER() before the outer WHERE rn <= 3 filter is applied. This respects SQL's processing order: the subquery computes row numbers first, and the outer query filters on that already-computed value. The PARTITION BY region ORDER BY sale_amount DESC, sale_id ensures exactly three rows per region, with ties broken cleanly by sale_id. Option A fails because it tries to use ROW_NUMBER() directly inside WHERE, which is illegal — the window function hasn't been evaluated at the WHERE processing stage. Option B makes the same mistake with HAVING, and also incorrectly uses GROUP BY on individual columns when no aggregation is needed. Putting a window function in HAVING doesn't save you — HAVING filters aggregated groups, not window-function results. Option D uses RANK() instead of ROW_NUMBER(), which is structurally similar to C but breaks the requirement. RANK() can return more than three rows per region when there are ties in sale_amount, violating the "at most three rows" guarantee. Study tip: Whenever you need to filter on a window function result, your instinct should be "subquery first, filter second." If you see a window function in WHERE or HAVING without a subquery, that's an automatic red flag.

Question 8

A Results table contains four rows in one division with scores 100, 90, 90, and 80. The query calculates ROW_NUMBER(), RANK(), and DENSE_RANK() over ORDER BY score DESC.

Which values are assigned to the row whose score is 80?

  1. ROW_NUMBER = 4, RANK = 3, and DENSE_RANK = 3
  2. ROW_NUMBER = 4, RANK = 4, and DENSE_RANK = 3 (correct answer)
  3. ROW_NUMBER = 3, RANK = 4, and DENSE_RANK = 3
  4. ROW_NUMBER = 4, RANK = 4, and DENSE_RANK = 4
Explanation: When working with SQL window ranking functions, you need to understand how each one handles ties differently — this is one of the most common exam traps. Here's how each function works across your four rows (scores: 100, 90, 90, 80): ROW_NUMBER() assigns a unique sequential integer to every row, regardless of ties. No duplicates, ever. So your rows get 1, 2, 3, 4 — the score-80 row is always 4. RANK() gives tied rows the same rank, then skips the next rank(s). The two scores of 90 both receive rank 2, so rank 3 is skipped entirely, and the score-80 row jumps to rank 4. DENSE_RANK() also gives tied rows the same rank, but does not skip ranks afterward. The two 90s share rank 2, and score-80 gets rank 3 — no gaps. This confirms answer B: ROW_NUMBER = 4, RANK = 4, DENSE_RANK = 3. Choice A is wrong because it assigns RANK = 3, which would only be correct if RANK didn't skip — that's actually DENSE_RANK behavior. Choice C is wrong on two counts: ROW_NUMBER cannot be 3 here (that belongs to the second score-90 row), and RANK would still be 4, not 4 being the issue but ROW_NUMBER being incorrect. Choice D is wrong because it assigns DENSE_RANK = 4, confusing it with RANK — DENSE_RANK never skips, so it can't reach 4 with only three distinct score values. Memory tip: Think Dense = No gaps, RANK = skips like a skipped grade in school.

Question 9

A competition has scores 95, 95, 90, 85, and 85. The following value is calculated for each competitor: RANK() OVER (ORDER BY score DESC) AS position. An outer query keeps rows where position <= 2.

Which set of competitors is returned?

  1. Only the two competitors who scored 95 (correct answer)
  2. The two competitors at 95 and the competitor at 90
  3. The competitors at 95, 90, and both competitors at 85
  4. One competitor at 95 and the competitor at 90
Explanation: When working with window functions like RANK(), the key concept to internalize is how ties are handled and what rank numbers are actually assigned. RANK() assigns the same rank to tied rows, then skips subsequent ranks. So with scores 95, 95, 90, 85, 85, the assignments are: both competitors at 95 receive rank 1, the competitor at 90 receives rank 3 (not 2, because rank 2 is skipped), and both competitors at 85 receive rank 4. When your outer query filters WHERE position <= 2, only rows with rank 1 or rank 2 qualify — meaning only the two competitors who scored 95 are returned, since no competitor actually holds rank 2. That confirms A is correct. Choice B is the most tempting trap — you might assume rank 2 belongs to the 90 scorer, but RANK() skips numbers after a tie, so 90 lands at rank 3, which fails the <= 2 filter. Choice C describes a scenario where every competitor is returned, which would only make sense if the filter were removed entirely or set much higher. Choice D reflects confusion about how ties work — it implies only one of the two 95 scorers is returned, but since both share rank 1, both pass the filter equally. A useful rule of thumb: RANK() can produce gaps in rank numbers, while DENSE_RANK() never skips. If this question used DENSE_RANK(), the 90 scorer would hold rank 2 and would be included. Always identify which ranking function is used before reasoning about which rows a filter will capture.

Question 10

A CTE first groups sales by department and produces totals: department A has 500, departments B and C each have 400, and department D has 100. A second query calculates both RANK() OVER (ORDER BY total DESC) and DENSE_RANK() OVER (ORDER BY total DESC) over those grouped rows.

Which pair of rankings is assigned to department D?

  1. RANK = 3 and DENSE_RANK = 3
  2. RANK = 4 and DENSE_RANK = 3 (correct answer)
  3. RANK = 4 and DENSE_RANK = 4
  4. RANK = 3 and DENSE_RANK = 4
Explanation: When working with window ranking functions, the key distinction to understand is how each handles ties. RANK() skips position numbers after a tie, while DENSE_RANK() never skips — it always increments by exactly one. Here's how the four departments sort by total descending: A (500) is 1st, then B and C are tied at 400, and D sits last at 100. Both RANK() and DENSE_RANK() assign 1 to department A. For the tie between B and C, both functions assign 2 to each. Now the behavior diverges: RANK() has already "used up" positions 2 and 3 for the tied pair, so the next row — department D — receives rank 4. DENSE_RANK(), however, simply moves to the next consecutive rank after 2, giving D a dense rank of 3. That makes the correct answer B. Choice A is wrong because it assigns RANK = 3 to D, ignoring that the tie between B and C consumes two rank positions (2 and 3), pushing D to position 4. Choice C incorrectly assigns DENSE_RANK = 4, which would only happen if there were no ties at all — dense rank never skips. Choice D reverses the logic entirely, swapping the values of the two functions, which reflects a fundamental misunderstanding of how each works. A reliable memory trick: think of DENSE_RANK as counting distinct rank levels (no gaps), while RANK counts actual row positions (gaps appear after ties). On SQL exams, tie-handling questions almost always hinge on this single difference.