Postgres in depth · Chapter 26 of 65

Leaderboards

By the end of this chapter you can number and rank rows with window functions, decide what happens to ties, rank inside each group, and pick the top N of every group. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.

  • Part: Subqueries, CTEs and windows
  • 4 tasks
  • PostgreSQL
  • In the all labs plan

The first 2 chapters of Postgres in depth are free. This one comes with the all labs plan.

Why you need this

Kabir wants leaderboards everywhere: the most popular courses on the home page, the top learners of the month, each category's best seller, each instructor's two best courses for their profile page. Arjun asks the question that makes it hard: "and when two are equal?"

GROUP BY can count, but it collapses the rows: you get one row per group and lose the rows inside it. ORDER BY with LIMIT 3 gives three rows, not three places, and cannot restart for every category. A ranking needs each row to know about the other rows around it, while staying a row of its own.

What you build: 4 tasks

Each task has tests. You write the code in the lab's editor and press Check.

  1. Task 1

    popular.sql: the most popular courses

    Rank every published course by its number of enrollments, most first. Courses with the same number share a place, and the next place skips: two courses on place 3 are followed by place 5.

    Return three columns: title, enrollments and place. Order by place, then title.

    Careful: the edge data has a published course nobody enrolled in. It is on the list too, with 0 enrollments, in last place.

  2. Task 2

    top-learners.sql: the top three places

    Kabir wants a leaderboard of the students who completed the most lessons (lesson_progress rows with completed_at). Places have no gaps: two students on place 1 are followed by place 2. Show everyone on places 1, 2 and 3, however many students that is.

    Return three columns: name, lessons_completed and place. Order by place, then name.

    Careful: this is not the first three rows. Ties put several students on one place.

  3. Task 3

    category-best.sql: each category's best seller

    For every category, find its best-selling published course: the one with the most enrollments. When two courses in a category tie for the most, show both.

    Return three columns: category (the category's name), title and enrollments. Order by category, then title.

    Courses nobody enrolled in are left out, and so is a category none of whose published courses has an enrollment.

  4. Task 4 · interview question

    Interview: top-two.sql, each instructor's top two courses

    A course's revenue is the sum of price_paid over its enrollments. For every instructor, list their two highest-earning courses, numbered 1 and 2; when two courses earn the same, the one whose title comes first alphabetically gets the lower number. An instructor with one course shows one row.

    Return four columns: instructor (the instructor's name), title, revenue and place. Order by instructor, then place.

    Count every course that has at least one enrollment, whatever its status. A free course has revenue 0 and still counts.

What you will learn

  • Window functions: a calculation over related rows, written with OVER, that keeps every row.
  • row_number, rank and dense_rank: three answers to "what about ties?"
  • PARTITION BY: a separate ranking for each group.
  • Top N per group: why the filter needs a second step.
  • When windows run: after WHERE and GROUP BY, before ORDER BY.

More in Subqueries, CTEs and windows

Build "Leaderboards" yourself

LCO, an edtech company, moves its platform onto a single Postgres server you run. A real editor and terminal in your browser, AI that explains, and a Check that says when it works.

The first 2 chapters of Postgres in depth are free. This one comes with the all labs plan.