SQL from zero · Chapter 20 of 37
The latest of everything
By the end of this chapter you can pick the first row of every group, or the top few rows of every group, in one short 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 a "what's new" strip on every profile: each member's newest post, and on the full profile their three newest. Rohan wants each tag's most-liked post for the explore page.
You can already do the first with last chapter's tools: number every post with ROW_NUMBER() OVER (PARTITION BY ...) in a CTE, then keep row 1 outside it. That numbers every post of every member just to keep one, it is three pieces to get right, and for "three newest, members with no posts included" you need one more join on top, or the members who never posted quietly vanish.
What you build: 4 tasks
Each task has tests. You write the code in the lab's editor and press Check.
- Task 1
latest-post.sql: each active member's newest post
Each active member's newest post, one row per member. Only members who have not left the site, and only posts that are not deleted. If two of a member's posts were written at exactly the same moment, the one with the higher id wins. Members with no such posts do not appear.
Return three columns: username, content and created_at. Any order.
- Task 2
three-newest.sql: up to 3 newest posts per member, members with none included
Every active member (members who have not left the site) with their 3 newest posts that are not deleted: newest first, and the higher id first when two posts share a moment. A member with fewer posts shows what they have. A member with no such posts appears once, with an empty (NULL) content and created_at.
Return three columns: username, content and created_at. Any order.
- Task 3
top-post-per-tag.sql: each tag's most-liked post
Each tag's most-liked post, one row per tag, counting only posts that are not deleted. If two posts of a tag tie on likes_count, the newer one (the later created_at) wins. Tags with no live posts do not appear. A post with two tags can be the top post of both.
Return three columns: tag (the tag's name), content and likes_count. Any order.
- Task 4 · interview question
Interview: second-highest.sql, the second-highest likes_count
The second-highest likes_count among posts that are not deleted. If several posts share the top value, the second-highest is the next value down, not the top value again.
Return one row with one column: second_highest.
What you will learn
- DISTINCT ON: keep the first row of each group, where "first" is whatever ORDER BY says.
- LATERAL: a subquery that runs once per outer row and can use its columns, so "the top 3 of each" is a LIMIT 3.
- LEFT JOIN LATERAL ... ON true: the same, keeping the rows whose subquery found nothing.
- Values, not rows: the second-highest value when the top one is tied.
More in Analytics
Build The latest of everything 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.