SQL from zero · Chapter 5 of 37
One page at a time
By the end of this chapter you can sort rows in an order that never changes and serve them one page at a time, with OFFSET or with a cursor. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.
- Part: First queries
- 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's feed is live, and nobody wants 121 posts at once. She wants five at a time, and the order must never jump around while someone scrolls: no post shown twice, none skipped.
ORDER BY created_at DESC LIMIT 5 gives the first page, but you have no way yet to ask for the second. And when two rows tie on the sort key, Postgres may return them in either order, so a post at the edge of a page can appear on two pages, or on none.
What you build: 4 tasks
Each task has tests. You write the code in the lab's editor and press Check.
- Task 1
by-city.sql: active members sorted by city
List every active member (no date in deleted_at), sorted by city A to Z, with the members who have no city at the very top. Members in the same city, and the members with no city, are sorted by username A to Z.
Return two columns: username and city.
- Task 2
most-liked.sql: the 5 most liked posts
Find the 5 active posts (no date in deleted_at) with the most likes (likes_count), most first. Posts with the same likes: newest first (created_at). Posts with the same likes written at the same moment: highest id first.
Return three columns: content, likes_count and created_at.
- Task 3
page-two.sql: the second page of the feed
The feed shows the active posts (no date in deleted_at) newest first (created_at), and posts written at the same moment highest id first, 5 posts a page. Return the second page: posts 6 to 10, in feed order.
Return two columns: content and created_at.
- Task 4 · interview question
Interview: next-page.sql, the next page after a cursor
The members list shows every member, including those who have left the site, newest first (created_at), and members who joined at the same moment by username Z to A, 5 a page. The page on screen ended with the member who joined at 2026-03-01 00:00:00+05:30 with the username yash.
Return the next 5 members after that row, in list order, with keyset pagination: a condition on created_at and username together. OFFSET is not allowed (the check looks for the word).
Careful: another member may have joined at that exact moment with a username that comes before yash. They come next, so a condition on created_at alone would skip them.
Return two columns: username and created_at.
What you will learn
- Sorting by several keys: tie-breakers, a direction for each key, and where NULLs go.
- Pages with OFFSET: skip the rows of the earlier pages.
- Pages with a cursor: remember the last row shown and ask for what comes after it.
More in First queries
Build "One page at a time" 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.