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.

  1. 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.

  2. 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.

  3. 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.

  4. 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.