SQL from zero · Chapter 9 of 37
The weekly report
By the end of this chapter you can turn rows into columns: a table of months by post type, members sorted into buckets, and percentages that come out right. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.
- Part: Counting things
- 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 has a review on Monday and wants one slide with everything: what people post each month, which cities have the most verified members, how many posts actually get likes. She wants tables a person can read: one row per month, one column per post type.
GROUP BY month, type gets the numbers, but as a long list: 26 rows of month, type and count, which you would rearrange into a grid by hand. A month with no video posts has no row at all, so the zero is easy to miss. And a percent written the obvious way comes out wrong: in whole numbers, 2 verified members out of 3 is 66, not 66.7.
What you build: 4 tasks
Each task has tests. You write the code in the lab's editor and press Check.
- Task 1
type-pivot.sql: posts per month, one column per type
One row per month that has posts, counting only posts that are not deleted. Write the month as text like 2026-07. In each row, count that month's posts of each type: text, image, video, link and poll. A type with no posts that month shows 0.
Return six columns: month, text_posts, image_posts, video_posts, link_posts and poll_posts. Oldest month first.
- Task 2
verified-share.sql: verified members in each city
One row per city, leaving out members with no city: how many members live there, how many of them are verified (is_verified is true), and the percent who are verified, rounded to 1 decimal (2 of 3 is 66.7). Members who left still count.
Return four columns: city, members, verified and pct_verified. Any order.
- Task 3
like-buckets.sql: posts in each likes bucket
Sort the posts that are not deleted into buckets by likes_count: quiet (0 or 1 likes), liked (2 to 4 likes) and popular (5 likes or more), and count the posts in each bucket. A bucket with no posts does not appear.
Return two columns: bucket and posts. Any order.
- Task 4 · interview question
Interview: signups.sql, members who joined each month and stayed
For each month in which members joined (their created_at, as text like 2026-01): how many joined, how many of them are still active (deleted_at is NULL), and the percent still active, rounded to 1 decimal. Every member who ever joined counts in joined, including members who left.
Return four columns: month, joined, still_active and pct_active. Oldest month first.
What you will learn
- FILTER and CASE inside an aggregate: count only some of a group's rows, one column per kind of row. That is a pivot.
- Buckets with CASE: make your own groups (quiet, liked, popular) and group by them.
- Percentages: divide with a decimal so the fraction survives, then round.
More in Counting things
Build The weekly report 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.