Postgres in depth · Chapter 23 of 65
Questions inside questions
By the end of this chapter you can answer a question that needs another answer first, by putting one query inside another, and combine the rows of two queries into one list. 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
Kabir's questions have grown. "Which courses cost more than the usual course in their category?" needs the usual price first. "Who enrolled in React from Zero without ever taking Complete JavaScript?" asks, for every enrollment, whether some other enrollment is missing. Sana's refer-a-friend reward needs the students who appear nowhere in another column.
Joins and GROUP BY answer one question at a time. These are two questions, one inside the other, and SQL lets you write them exactly like that.
What you build: 4 tasks
Each task has tests. You write the code in the lab's editor and press Check.
- Task 1
category-stars.sql: priced above their category
Kabir wants the published courses that cost more than the average price of the published courses in their own category.
Return three columns: title, category (the category's name) and price. Order by category, then price from highest to lowest, then title.
Careful: the average is worked out again for each course's own category, not once over every course. A course alone in its category equals its average, so it is not above it.
- Task 2
skipped-prerequisite.sql: who skipped the prerequisite
Some students enrolled in a course without ever enrolling in a course it requires (course_prerequisites says which course requires which). List every such case: one row per enrollment and missing prerequisite.
Return three columns: name (the student's), course (the title of the course they enrolled in) and missing (the title of the prerequisite they never enrolled in). Order by name, course, missing.
Careful: when or in which order they enrolled does not matter, only whether they enrolled in the prerequisite at all. A course with two prerequisites can be missing one, both or neither.
- Task 3
free-only.sql: students who took only free courses
Kabir wants to send a discount to the students who are enrolled in at least one free course (price 0) and in no paid course.
Return two columns: name and email. Order by name.
Careful: the edge data has a student whose paid order was refunded, so she has no enrollment in a paid course; she belongs in the list.
- Task 4 · interview question
Interview: never-referred.sql, students who never referred anyone
LCO is starting a refer-a-friend reward. Sana wants every student who is not deleted (deleted_at is empty) and has never referred anyone: nobody's referred_by points at them.
Return two columns: name and email. Any order.
Careful: most students were not referred by anyone, so referred_by is mostly NULL. A test against a list that contains a NULL can answer neither yes nor no.
What you will learn
- Subqueries: a whole query in brackets, used as a value, a list or a table.
- Correlated subqueries: an inner query that looks at the outer row, so it answers again for every row.
- EXISTS and NOT EXISTS: "is there at least one matching row?"
- The NOT IN trap: why one NULL in a list empties your answer.
- UNION, INTERSECT and EXCEPT: two queries' rows added, overlapped or subtracted.
More in Subqueries, CTEs and windows
Build "Questions inside questions" 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.