Postgres with Drizzle · Chapter 19 of 40
Window into the data
By the end of this chapter you can add rankings, numbering, running totals and the previous or next row's value to every row, without losing a single row. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.
- Part: Advanced Queries
- 5 tasks
- PostgreSQL
- In the all labs plan
The first 2 chapters of Postgres with Drizzle are free. This one comes with the all labs plan.
Why you need this
Meera wants a leaderboard: each member with their place, ties sharing a place. Rohan wants each author's posts numbered newest first, a running total of likes over time, and each post beside the likes of the post before and after it.
Chapter 18's groupBy cannot do this: it melts the rows of a pile into one answer row, and the leaderboard needs every member. Without something new you would fetch the rows sorted and number them in a JavaScript loop, keeping counters and the previous row by hand, and do it again for every new question. A window function keeps every row and adds a column computed from the rows around it.
What you build: 5 tasks
Each task has tests. You write the code in the lab's editor and press Check.
- Task 1
rankUsersByPostCount: a leaderboard with shared places
Write rankUsersByPostCount(). It returns every member with rank, their place by the stored post_count column, most posts first. Tied members share a place and the next place is skipped, as in a race: 10, 5 and 5 posts give 1, 2 and 2.
Send raw SQL with getPool().query and return result.rows: objects with id, username, post_count and rank (rank may be a string such as '2'). Any order: the check sorts by rank.
- Task 2
getPostsWithRowNumbers: each author's posts numbered
Write getPostsWithRowNumbers(). It returns every post with row_num: its number among its own author's posts, newest first, so each author's newest post is 1 and the numbering starts again at 1 for every author.
Return result.rows: objects with id, content, author_id and row_num. Any order.
- Task 3
getPostsWithRunningLikeTotal: likes adding up over time
Write getPostsWithRunningLikeTotal(). It returns every post with running_total: its own likes_count plus the likes_count of every post created before it. With posts of 10, 20 and 30 likes created in that order, the totals are 10, 30 and 60.
Return result.rows: objects with id, content, likes_count and running_total, in order of created_at, oldest first: the check reads the rows in that order.
- Task 4
getPostsWithLagLead: the posts before and after
Write getPostsWithLagLead(). It returns every post with prev_likes, the likes_count of the post created just before it, and next_likes, the likes_count of the post created just after it. The first post's prev_likes and the last post's next_likes are null.
Return result.rows: objects with id, content, likes_count, prev_likes and next_likes, in order of created_at, oldest first: the check reads the rows in that order.
- Task 5
getDenseRankByLikes: places with no gaps
Write getDenseRankByLikes(). It returns every post with dense_rank, its place by likes_count, most likes first. Tied posts share a place and no place is skipped: 20, 10, 10 and 5 likes give 1, 2, 2 and 3.
Return result.rows: objects with id, content, likes_count and dense_rank (it may be a string). Any order: the check sorts by likes_count.
What you will learn
- OVER (ORDER BY ...): computes a value for each row from the rows in an order, keeping every row.
- ROW_NUMBER, RANK and DENSE_RANK: three ways to number rows, which differ only at ties.
- PARTITION BY: start again for each group.
- SUM(...) OVER, LAG and LEAD: a running total, and the previous and next row's value.
More in Advanced Queries
Build "Window into the data" yourself
MingleSphereQL grows up: its backend talks to Postgres through Drizzle. 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 with Drizzle are free. This one comes with the all labs plan.