SQL from zero · Chapter 18 of 37
Leaderboards
By the end of this chapter you can number and rank rows, handle ties the way you mean to, and pick the top rows of every group in one query. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.
- Part: Analytics
- 4 tasks
- SQL
- In the all labs plan
The first 2 chapters of SQL from zero are free. This one comes with the all labs plan.
Why you need this
Meera wants leaderboards for the app: top posts, top posters, and the stars of every city. People love a ranking, with a number next to each name.
With what you know so far, ORDER BY likes_count DESC LIMIT 4 gives the order but no numbers, and it hides ties: three posts tie on 7 likes, and LIMIT 4 keeps two of them without saying which or why. GROUP BY folds rows together, so a member's posts disappear into one count. And "top two per city" means one query per city, nine of them, rewritten the day a tenth city appears.
What you build: 4 tasks
Each task has tests. You write the code in the lab's editor and press Check.
- Task 1
like-ranks.sql: every post ranked by likes
Every post that is not deleted (by any member, members who left included), ranked by likes_count, most liked first. Posts with the same likes_count share a rank, and the next rank skips ahead, like a sports table: 1, 1, 3.
Return four columns: username, content, likes_count and like_rank. Any order.
- Task 2
poster-places.sql: members placed by post count, no gaps
Every member with at least one post that is not deleted (members who left included), with how many such posts they have, and their place: 1 for the most posts. Members with the same count share a place, and the places after a tie do not skip: 1, 2, 2, 3.
Return three columns: username, posts and place. Any order.
- Task 3
top-post.sql: each member's most-liked post
Each member's most-liked post, one row per member, counting only posts that are not deleted. If two of a member's posts tie on likes_count, pick the older one (the earlier created_at). Members with no live posts are left out; members who left the site are included.
Return three columns: username, content and likes_count. Any order.
- Task 4 · interview question
Interview: city-top-two.sql, the 2 most-liked members of each city
For each city, its 2 most-liked members. A member's likes are the total likes_count of their posts that are not deleted, and 0 for a member with no such posts: they still count. When two members of a city have the same likes, the username that comes first A to Z wins. A city with one member shows just that member. Leave out members with no city; members who left the site are included.
Return three columns: city, username and likes. Any order.
What you will learn
- Window functions: a value computed over other rows, added as a column, while every row stays.
- ROW_NUMBER, RANK and DENSE_RANK: three ways to number rows, which differ only on ties.
- PARTITION BY: the numbering restarts for each group, so "the top one per member" is a single query.
- Filtering on a rank: in a CTE or subquery, because WHERE runs before windows.
More in Analytics
Build "Leaderboards" yourself
MingleSphereQL, a social app, needs answers from its data every day. A real editor and terminal in your browser, AI that explains, and a Check that says when it works.
The first 2 chapters of SQL from zero are free. This one comes with the all labs plan.