ROW_NUMBER, RANK & DENSE_RANK — Concept Check
Questions on ROW_NUMBER, RANK & DENSE_RANK. ROW_NUMBER assigns a unique sequential integer to every row; RANK skips positions after ties; DENSE_RANK never skips — choose based on whether gaps in ranking matter.
Questions in this quiz
8 questions on ROW_NUMBER, RANK & DENSE_RANK — Concept Check. Work through them here, then start the quiz to answer them and see your score.
1.In a company database, you want to list employees by their salary within each department. If two employees have the same salary, what will be the next rank assigned using the RANK() function?
- AThe next rank will be skipped by the number of ties.
- BThe next rank will be determined by alphabetical order of names.
- CThe next rank will be the immediate next integer.
- DThe next rank will be the same as the tied rank.
2.When using DENSE_RANK() to rank products by sales within categories, what happens if two products have the same sales?
- AThey receive ranks based on alphabetical order of product names.
- BThey receive the same rank, and the next rank is skipped.
- CThey receive different ranks based on product ID.
- DThey receive the same rank, and the next rank is the immediate next integer.
3.A developer uses ROW_NUMBER() for pagination but notices gaps in the sequence. What could be the reason?
- AGaps occur due to incorrect partitioning.
- BGaps occur because ROW_NUMBER() skips numbers after ties.
- CGaps occur if the ORDER BY clause is non-deterministic.
- DROW_NUMBER() always produces distinct values, so gaps are not possible.
4.Which function would you use to ensure exactly N rows are returned per group without ties?
- ARANK()
- BDENSE_RANK()
- CROW_NUMBER()
- DCOUNT()
5.In a sales report, two products have the same quantity sold. Using RANK(), what rank will the next product receive?
- AThe rank will be the same as the tied products.
- BThe rank will depend on the product ID.
- CThe rank will be the immediate next integer.
- DThe rank will be skipped by the number of ties.
6.Why might someone incorrectly choose DENSE_RANK() when they need exactly N rows per group?
- AThey confuse DENSE_RANK() with COUNT().
- BThey assume DENSE_RANK() is faster than ROW_NUMBER().
- CThey believe DENSE_RANK() always returns distinct rows.
- DThey think DENSE_RANK() skips ranks like RANK().
7.A report requires listing top 3 employees by salary per department, including ties. Which function should you use?
- AROW_NUMBER()
- BDENSE_RANK()
- CSUM()
- DRANK()
8.What is a key difference between ROW_NUMBER() and RANK() when handling ties?
- ABoth functions handle ties by skipping positions.
- BROW_NUMBER() and RANK() both produce distinct values.
- CROW_NUMBER() skips positions after ties, while RANK() produces distinct values.
- DROW_NUMBER() always produces distinct values, while RANK() skips positions after ties.