SQL from zero · Chapter 12 of 37

Who follows whom

By the end of this chapter you can join the same table twice, and join a table to itself, to answer questions about two people in one row: who follows whom, who follows back, who replied to whom. 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 wants a friends badge: when two members follow each other, both see it. Every row of follows holds two people, follower_id and followee_id, and both point at users. To find friends you need two rows of the same table side by side: a follow, and the one going back.

Every join so far paired a row with a row of a different table. With only that, you would list Rohan's follows, then his followers, and compare the two lists by eye. Then do it again for each of the 24 members.

What you build: 5 tasks

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

  1. Task 1

    follow-names.sql: every follow, as two usernames

    List every follow as usernames: who follows, and who is followed. Every row of follows appears once, including follows by or of members who left.

    Return two columns: follower and followee. Any order.

  2. Task 2

    meera-followers.sql: who follows meera

    List the members who follow meera, by username. Members who left count too.

    Return one column: username. Sort A to Z.

  3. Task 3

    mutual-follows.sql: pairs who follow each other

    List every pair of members who follow each other. Each pair appears once: user_a is the username that comes first A to Z, user_b is the other one.

    Return two columns: user_a and user_b. Any order.

  4. Task 4

    reply-authors.sql: who replied to whom

    List every reply (a comment whose parent_id is set, replies to replies included): its content, the username of who wrote it, and the username of who wrote the comment it answers.

    Return three columns: reply, replier and replied_to. Any order.

  5. Task 5 · interview question

    Interview: no-follow-back.sql, follows that were never returned

    List every follow that was never returned: the follower follows the followee, but the followee does not follow the follower back.

    Return two columns: follower and followee (usernames). Any order.

What you will learn

  • The same table, twice: join users once for each person in the row, with a different alias each time.
  • A table joined to itself: pair a follow with the follow going back, or a reply with the comment it answers.
  • Each pair once: compare the names to drop the mirror copy.
  • The follows nobody returned: last chapter's anti-join, on a self join.

More in Joins

Build "Who follows whom" 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.