Postgres in depth · Chapter 25 of 65
Discussion threads
By the end of this chapter you can walk a tree or a chain of any depth with a recursive CTE, carry the depth and the path as you go, put a tree in reading order, and stop safely when the data has a loop. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.
- Part: Subqueries, CTEs and windows
- 4 tasks
- PostgreSQL
- In the all labs plan
The first 2 chapters of Postgres in depth are free. This one comes with the all labs plan.
Why you need this
LCO's lessons have discussions. A student asks a question, someone replies, someone replies to the reply. Each comment stores only its parent_id, the comment it answers. Kabir wants the forum page to show each thread indented, the way people read it.
With joins you can find a reply to a reply: comments joined to comments, twice. But how many joins? The threads are four deep today and six deep in the edge data, and tomorrow someone replies again. The same shape is everywhere at LCO: the category tree, prerequisites that have prerequisites, students who referred students who referred students. Each needs a query that repeats until there is nothing left to find.
What you build: 4 tasks
Each task has tests. You write the code in the lab's editor and press Check.
- Task 1
depths.sql: every comment's depth
Comments answer each other: parent_id is the comment a reply answers, and it is empty for a comment that starts a thread. Give every comment its depth: 1 for a comment that starts a thread, 2 for a reply to it, 3 for a reply to that, and so on.
Return two columns: id (the comment's) and depth. Any order. Every comment appears exactly once.
Careful: the main data's threads are 4 deep and the edge data has one 6 deep. A fixed number of self joins stops too early: the query must follow the replies as deep as they go.
- Task 2
thread-view.sql: the threads in reading order
The forum shows every thread the way people read it: a comment, then its replies, each followed by its own replies, before the next comment at the same level. Comments with the same parent, and the comments that start threads, come oldest first (created_at), and by id when two were posted at the same moment.
Return three columns: id, depth (as in task 1) and line: two spaces for every level below the top, then the author's name (the student's), a colon and a space, and the body. For example a reply at depth 2 by Diya Patel reads two spaces, then Diya Patel, a colon, a space and the body. Order: reading order, as described.
Careful: sorting by created_at, by id or by depth does not give reading order: replies are posted later than other threads. Each row has to carry the way down to it from the top.
- Task 3
prerequisites.sql: everything a course needs
A course needs its prerequisites, their prerequisites, and so on. For every course that needs at least one other course, count how many different courses it needs, directly or through others.
Return two columns: title and needs. Order by needs from most to fewest, then title.
Careful: a course can reach the same prerequisite along two paths; count it once.
- Task 4 · interview question
Interview: referral-loop.sql, a network with a loop
List everyone in Ishaan Gupta's referral network: the students he referred (level 1), the students they referred (level 2), and so on.
Return two columns: name and level. Order by level, then name. Ishaan himself is not in the list.
Careful: Sana's last import made a loop. The Check first sets Ishaan's referrer to Yash Verma, and Yash's referrer to Ishaan, so each referred the other. Your query must stop at the loop and list each student once, at the level where they first appear. Your own database has no loop; make one with two updates to try it.
What you will learn
- WITH RECURSIVE: a start query and a step that repeats on its own results.
- Depth and path: values carried down from row to row.
- Reading order: sorting a tree by its path, or with SEARCH.
- Loops: why recursion can run for ever, and the CYCLE clause.
More in Subqueries, CTEs and windows
Build "Discussion threads" yourself
LCO, an edtech company, moves its platform onto a single Postgres server you run. A real editor and terminal in your browser, AI that explains, and a Check that says when it works.
The first 2 chapters of Postgres in depth are free. This one comes with the all labs plan.