SQL from zero · Chapter 16 of 37

Step by step

By the end of this chapter you can write a long query as a list of small named steps, run each step on its own, and use one step twice. 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 has a board meeting on Friday. She needs an engagement report: for every member, their posts, the likes and comments those posts got, and how much engagement one post brings in.

Rohan tried it as one query: posts joined to likes and to comments in one go. It says Anya's live posts got 101 likes and 97 comments. They really got 42 and 13. With what you know so far, the only other way is subqueries inside subqueries: forty lines of brackets, no way to run the middle part on its own to see where the numbers go wrong, and the same subquery pasted twice when you need it twice (once for the rows, once for their average).

What you build: 4 tasks

Each task has tests. You write the code in the lab's editor and press Check.

  1. Task 1

    busy-members.sql: members who post more than the average poster

    Count each member's posts that are not deleted. Then work out the average of those counts, only over members who have at least one such post (members with no posts do not pull the average down). Return the members whose count is higher than that average. Members who left the site still count, both in the average and in the answer.

    Return two columns: username and posts. Any order.

  2. Task 2

    engagement.sql: Meera's engagement report

    One row per member who has at least one post that is not deleted, members who left the site included. Count only those live posts, everywhere: posts is how many of them the member has, likes is how many likes they got, comments is how many comments they got (replies count too). likes and comments are 0 when there are none. per_post is (likes + comments) divided by posts, as a decimal, rounded to 2 decimals.

    Return five columns: username, posts, likes, comments and per_post. Any order.

  3. Task 3

    city-stars.sql: members with more followers than their city's average

    Count each member's followers: the rows in follows where they are the one being followed. A member nobody follows has 0, and that 0 counts in the average. Then return the members whose follower count is higher than the average follower count of all the members in their city. Leave out members with no city. Members who left the site still count, in their city's average and in the answer.

    Return three columns: username, city and followers. Any order.

  4. Task 4 · interview question

    Interview: likes-balance.sql, members who received more likes than they gave

    For every member (members who left the site included): given is how many likes they gave, and received is how many likes their posts got, on all their posts, deleted ones too. Both are 0 when there are none. Return the members who received strictly more likes than they gave.

    Return three columns: username, given and received. Any order.

What you will learn

  • WITH: give a query a name for one statement, then read from it like a table, as many times as you like.
  • Several steps in one WITH: each step reads the ones above it.
  • Counting before joining: why joining two lists to the same post multiplies rows, and how separate steps fix it.

More in Analytics

Build "Step by step" 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.