SQL from zero · Chapter 6 of 37
Counting everything
By the end of this chapter you can turn many rows into one number: how many there are, how much in total, the average, the first and the last. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.
- Part: Counting things
- 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
Rohan is building MingleSphere's admin dashboard. It has five tiles, and each tile shows one number about the whole site: how many members there are, the total likes on posts, how many messages are still unread, when the first post was written, how long bios are on average.
Every query you have written so far returns one row per member or per post. Ask for the members and you get 24 rows back. Rohan does not want 24 rows: he wants the number 24.
What you build: 5 tasks
Each task has tests. You write the code in the lab's editor and press Check.
- Task 1
member-counts.sql: members, members with a city, cities
In one row, about every member (members who left count too): how many members there are, how many have a city filled in, and how many different cities they live in.
Return three columns: members, with_city and cities.
- Task 2
like-stats.sql: likes on active posts
In one row, about the posts that are not deleted and their likes_count: the total likes, the average likes per post rounded to 2 decimals, the most likes any one post has, and the fewest.
Return four columns: total_likes, avg_likes, most_likes and fewest_likes.
- Task 3
messages.sql: all, read and unread messages
In one row, about every message: how many messages there are, how many have been read (read_at has a time in it) and how many have not (read_at is NULL).
Return three columns: messages, read_messages and unread_messages.
- Task 4
post-range.sql: first and last active post, and their authors
In one row, about the posts that are not deleted: when the earliest one was written, when the latest one was written (both from created_at, as timestamps), and how many different members wrote them.
Return three columns: first_post, last_post and authors.
- Task 5
bio-lengths.sql: bio lengths, with and without the empty ones
In one row, about every member (members who left count too) and the length of their bio: how many members have a bio, the average length of the bios they wrote, and the average over every member where a member with no bio counts as length 0. Round both averages to 1 decimal.
Return three columns: with_bio, avg_bio and avg_everyone.
What you will learn
- Aggregate functions (count, sum, avg, min, max): many rows go in, one value comes out.
- How NULLs change the answer: an empty value is skipped, which can quietly change a count or an average.
- FILTER: counting only some of the rows, so one query can fill several tiles.
More in Counting things
Build "Counting 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.