SQL from zero · Chapter 13 of 37
Tags everywhere
By the end of this chapter you can join through a middle table to connect posts and tags, count and list through it without double counting, and find the posts that carry two tags at once. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.
- Part: Joins
- 5 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 is planning the explore page: every tag across the top with a count next to it, and a tap on a tag shows its posts. The poetry tag has to show too, with 0, even though nobody uses it yet.
A post can have many tags, and a tag can be on many posts. Neither posts nor tags can hold that, so a third table does: post_tags, one row per post and tag, just two ids. Every join so far went one step, from a row to the row it points at. A post's tag names are two steps away, and a post comes back once for every tag it has, so counts and lists double up unless you plan for it.
What you build: 5 tasks
Each task has tests. You write the code in the lab's editor and press Check.
- Task 1
post-tags.sql: every post with each of its tags
List every post that has tags, with the name of each tag: one row per post and tag, so a post with two tags gives two rows. Deleted posts count too.
Return two columns: content and tag. Any order.
- Task 2
tag-counts.sql: posts per tag, zeros included
For every tag, count how many posts carry it. Every tag is in the list, and a tag with no posts (poetry) shows 0. Deleted posts count too.
Return two columns: tag and posts. Any order.
- Task 3
untagged-posts.sql: posts with no tags
List the posts that have no tags at all, with the author's username. Deleted posts count too, and so do posts by members who left.
Return two columns: username and content. Any order.
- Task 4
member-tags.sql: each member's tags in one line
For each member whose posts have tags, list every tag on their posts in one text: each tag once, A to Z, separated by a comma and a space (like chai, cricket). Deleted posts count too. Members with no tagged posts do not appear.
Return two columns: username and tags. Any order.
- Task 5 · interview question
Interview: chai-and-databases.sql, posts tagged chai and databases
List the posts that carry both the chai tag and the databases tag (one of the two is not enough). Each post appears once. Deleted posts count too.
Return two columns: content and created_at. Any order.
What you will learn
- The middle table: two joins take you from a post, through post_tags, to its tag names.
- Rows that repeat: DISTINCT inside an aggregate counts or lists each value once.
- Outer joins through it: the tags with no posts, and the posts with no tags.
- Both, not either: the posts that carry two given tags.
More in Joins
Build "Tags everywhere" 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.