SQL from zero · Chapter 17 of 37

Threads and friends

By the end of this chapter you can walk a tree or a network to any depth with one query, and make rows that are not in any table, like every day of a month. 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 wants comment threads indented in the app: a reply to a reply to a reply, each one step further in. So every comment needs its depth. Rohan wants "people you may know": the members anya can reach through the people she follows, and the people they follow.

With what you know so far, each level is one more join of comments to comments (or follows to follows). Three levels means three joins, and you must know the deepest thread before you write the query. A thread one reply deeper than you planned is silently cut off. And for Meera's June chart, GROUP BY day only returns days that have posts: the empty days, the ones she most wants to see, are simply missing.

What you build: 4 tasks

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

  1. Task 1

    june-calendar.sql: every day of June 2026 with its posts

    List every day of June 2026, all 30 of them, with how many posts were written that day (India time, the lab's clock). Every post counts, deleted ones too. A day with no posts shows 0. day must be a date (like 2026-06-01), not a timestamp.

    Return two columns: day and posts. Order by day, 1 June first.

  2. Task 2

    comment-depth.sql: every comment with its depth

    Every comment, on every post, with its depth in its thread: 1 for a top-level comment (no parent_id), 2 for a reply to it, 3 for a reply to that reply, and so on, however deep the thread goes.

    Return three columns: content, created_at and depth. Any order.

  3. Task 3

    thread-paths.sql: every comment with its path of usernames

    Every comment, with path: the usernames of the authors from the top-level comment of its thread down to this comment, joined with a space, a greater-than sign and a space. A top-level comment's path is just its author's username; anya's reply to a comment by rohan has the path rohan > anya.

    Return three columns: content, created_at and path. Any order.

  4. Task 4 · interview question

    Interview: two-hops.sql, who anya reaches in two hops

    The members anya can reach by following follows: the people she follows are 1 hop away, and the people they follow are 2 hops away. Each member once, with the smallest number of hops it takes to reach them. Leave anya herself out (people she follows follow her back).

    Return two columns: username and hops. Any order.

What you will learn

  • WITH RECURSIVE: a CTE that reads its own rows, one level at a time, until a level finds nothing new.
  • Carrying values down: a depth that grows by 1, or a path of names that grows with every step.
  • Stopping: a limit on the steps, so a network with loops does not run forever.
  • generate_series: a list of days made from nothing, with the data joined onto it.

More in Analytics

Build "Threads and friends" 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.