RANK & DENSE_RANK functions

Lesson 9 of 13 in Coddy's SQL for advanced course.

ROW_NUMBER() is one type of ranking function, and there are two more: RANK() and DENSE_RANK().

The RANK() function numbers rows like ROW_NUMBER(), but it gives identical numbers for the same rows and skips numbers. DENSE_RANK() is similar to RANK(), but it does not skip numbers.

For example:

id	level
1	5
2	6
3	6
4	7
5	7
6	5

SELECT id, 
	ROW_NUMBER() OVER (ORDER BY level) as row_num,
	RANK() OVER (ORDER BY level) as row_rank,
	DENSE_RANK() OVER (ORDER BY level) as row_dense_rank,
FROM table1

This will return:

id	level	row_num	row_rank	row_dense_rank
1	5	1	1	1
2	6	2	2	2
3	6	3	2	2
4	7	4	4	3
5	7	5	4	3
6	5	6	6	4

The ROW_NUMBER() goes from 1 to 6 with unique values, the RANK() provides the same values for the same levels, but it skipped 3 and 5, and the DENSE_RANK() also provides the same values for the same levels but it doesn't skip any values.

Challenge

Medium

Specific imaginary regions receive medals. The success of a region is measured by how many medals they have in total and how many scores they got.

A vanadium medal represents 5 score

A silver medal represents 3 score

A bronze medal represents 1 score

Calculate the score of each region and the total number of medals they have, and name these columns total_score and total_medals respectively. densely rank the total_medals column and name this column final_rank.

Finally, order the result by the final_rank and the region and the

Try it yourself

WITH region_total_medals AS (
    SELECT region, SUM(medal_count) as total_medals
    FROM medals
    GROUP BY region
), region_prepare_total_score AS (
    SELECT region, medal_color, medal_count, DENSE_RANK() OVER (ORDER BY medal_color) as medal_rank
    FROM medals
), region_total_score AS (
    SELECT region, SUM((medal_rank*2 - 1)*medal_count) as medal_rank
    FROM region_prepare_total_score
    GROUP BY region
)

SELECT region_total_medals.region, 
        total_medals, medal_rank,
        DENSE_RANK() OVER (ORDER BY total_medals) as final_rank
FROM region_total_score
JOIN region_total_medals ON region_total_medals.region = region_total_score.region

All lessons in SQL for advanced

1Introdcution

Introduction Simplify queries, WITH keyword

4Summary

Final challenge #1

2Window Functions part 1

ROW_NUMBER function ORDER BY criterion PARTITION BY criterion LEAD & LAG functions Recap challenge #1 Recap challenge #2

3Window Functions part 2

RANK & DENSE_RANK functionsNTILE function Aggregation functions ROWS & RANGE criterion