Menu
Coddy logo textTech
START LEARNING
flag En iconEnglishdown icon

Recap - RANK & DENSE_RANK

Part of the Fundamentals section of Coddy's SQL journey. Lesson 66 of 72.

challenge icon

Challenge

Medium

Available tables and columns:

  • medals: region, medal_color, medal_count

Regions receive medals in competitions. Create a report that shows:

  1. Calculate for each region:
    • total_medals: The sum of all medals they have
    • rank_by_medals: Create a dense rank based on total_medals in ascending order (regions with fewer medals get better/lower ranks)
  2. Order the results by:
    • rank_by_medals (ascending)
    • region (alphabetically)

return:

  • region
  • total_medals
  • rank_by_medals

Try it yourself

All lessons in Fundamentals

1Introduction

IntroductionWhat is a databaseDatabase conceptsUnique values

4More Keywords

The IN keywordThe BETWEEN keywordThe LIKE keywordThe AS keywordRecap - Cellphone Models

7Dates

Handling Dates Part 1Handling Dates Part 2Handling Dates Part 3

10Imaginary Products

Adjust WattCalculate Product QualityProject lessonExpand To Other ProductsProject lessonCombine All ProductsProject lessonThe Overall AverageProject lesson

13Summary

Recap - CreaturesRecap - BooksRecap - Junk

2Conditions

Conditions BasicsThe AND keywordThe OR keywordThe NOT keywordMultiple Conditions CombinedParenthesisBooleans

5Arithmetic Operations

Mathematical OperatorsMathematical ColumnsThe Modulo OperationThe ROUND() Function

8Statistics

Built-In Aggregate Part 1Built-In Aggregate Part 2Grouping Part 1Grouping Part 2Subqueries Part 1Subqueries Part 2Recap - Total Gain ShopRecap - Scooter ShopRecap - Coffee Shop

11Window Functions part 1

ROW_NUMBER functionORDER BY criterionPARTITION BY criterionPARTITION & ORDERLEAD & LAG FunctionsRecap - LEAD & LAGRecap - PicturesRecap - Boxes

3Specific Return Format

Null valuesSort Results Part 1Sort Results Part 2Recap - Cyber Security FirmLimit number of recordsRecap - Vehicle Factory

6Intro Challenges

Recap - Parliamentary ElectionRecap - Police Criminal ArrestRecap - Bar Beverage ContainerRecap - Engineer new columns

9Multiple tables

Basic Join Part 1Basic Join Part 2Recap - JoinSelf joinRecap - Self JoinUnionSimplify queries, WITH keywordRecap - With QueriesRecap - Real Estate Contractor

12Window Functions part 2

RANK & DENSE_RANK FunctionsRecap - RANK & DENSE_RANKNTILE FunctionAggregation FunctionsROWS & RANGE Criterion

Practice on your own: SQL playground