Postgres in depth · Chapter 28 of 65

Latest lesson, fast pages

By the end of this chapter you can keep one row per group with DISTINCT ON, run a small query once for every row with LATERAL, and page through a big table with a keyset instead of OFFSET. 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

The LCO app is busy with "per row" questions. The home page shows each student the lesson they watched last. A course page shows its two newest reviews. The Continue button needs, for every enrollment, the first lesson not yet completed. And Sana's admin page, which lists students twenty at a time, gets slower the further she clicks.

Chapter 26 could answer the first three with a window and a filter, in two steps. Postgres has shorter and often faster tools for them, and the fourth needs a different idea of what "page 9,000" means.

What you build: 4 tasks

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

  1. Task 1

    latest-lesson.sql: each student's latest lesson

    The home page shows each student the lesson they touched last: their lesson_progress row with the latest updated_at.

    Return four columns: name (the student's), course (the course's title), lesson (the lesson's title) and updated_at. One row per student who has any progress. Any order.

  2. Task 2

    recent-reviews.sql: two recent reviews per course

    Each published course's page shows its two most recent reviews (latest created_at first; when two were written at the same moment, the reviewer whose name comes first alphabetically wins the place). A course with fewer reviews shows what it has, and a course with none still appears once, with empty review columns.

    Return four columns: title (the course's), student (the reviewer's name), rating and created_at. Any order.

    Careful: in the main data, two reviews of one course share the same moment. In the edge data, a published course has no review at all.

  3. Task 3

    next-lesson.sql: continue where you left off

    The Continue button takes a student to the first lesson of the course, in course order (section position, then lesson position), that they have not completed (no lesson_progress row with completed_at for it). Find it for every enrollment.

    Return three columns: name (the student's), course (the title) and next_lesson (the lesson's title). Any order.

    Enrollments in which every lesson is completed have no next lesson and are left out.

  4. Task 4 · interview question

    Interview: next-page.sql, the next page by keyset

    The admin's students page lists students newest signup first, five per page; students who signed up at the same moment are ordered by email, from z to a. The current page ends with Diya Patel. Return the next page: the five students that come right after her in that order.

    Return three columns: name, email and signed_up_at. Order: the page's order.

    Careful: do not count rows to skip. The Check adds new students before running your file again, and the page must still start right after Diya Patel. In the edge data, two students signed up at the same moment right after her.

What you will learn

  • DISTINCT ON: the first row of every group, in one step.
  • LATERAL: a subquery in FROM that can see the rows before it, so it runs per row, with its own ORDER BY and LIMIT.
  • LEFT JOIN LATERAL ... ON true and CROSS JOIN LATERAL: keeping or dropping rows the small query finds nothing for.
  • Keyset paging: WHERE (a, b) < (x, y) instead of OFFSET, and why it stays fast.

More in Subqueries, CTEs and windows

Build "Latest lesson, fast pages" 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.