Postgres in depth · Chapter 22 of 65
Courses nobody bought
By the end of this chapter you can keep the rows a join would drop: list every course with its students even when it has none, find the rows that have no match at all, avoid the filter that quietly undoes an outer join, and combine every row with every row on purpose. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.
- Part: Answering LCO's questions
- 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 is planning the next sale and asks the uncomfortable questions: which courses has nobody bought, which students signed up and never enrolled, how did each course do in September, zeros included. Arjun wants signups for every month, also the months with none.
Chapter 21's inner joins cannot answer any of these. They keep only rows that found a partner, so a course with no enrollments vanishes from the answer instead of showing a 0. And a question about what is missing cannot be answered by a query that throws the missing rows away.
What you build: 4 tasks
Each task has tests. You write the code in the lab's editor and press Check.
- Task 1
course-students.sql: every course and its students
Kabir wants every course (any status) with its number of students, including the courses nobody has enrolled in, which show 0. Return title, status and students (how many enrollments the course has).
Return three columns: title, status, students. Order: fewest students first, then title A to Z.
Careful: on the edge data nobody is enrolled in Docker Basics. It must appear with 0, not vanish and not show 1.
- Task 2
never-enrolled.sql: students who never enrolled
Sana wants to welcome the students who signed up but never enrolled in any course. List every student who has not been deleted and has no enrollment at all: name and signed_up_at.
Return two columns: name, signed_up_at. Order: earliest signup first, then name A to Z.
- Task 3
september-sales.sql: September for every published course
Kabir's September report covers every published course, including the ones with no September sales. For each, return title, enrollments (how many of its enrollments were made in September 2026, India time) and revenue (the sum of their price_paid, 0 when none).
Return three columns: title, enrollments, revenue. Order: highest revenue first, then title A to Z.
Careful: a September condition in the wrong place throws away every course with no September enrollment.
- Task 4 · interview question
Interview: signup-calendar.sql, every month, empty ones too
Arjun's chart needs one bar per month from January to September 2026, even for a month with no signups. Return month (the first day of the month, as a date) and signups (how many students signed up in that month, India time, deleted students included; 0 when none).
Return two columns: month, signups. Order: month, earliest first.
Careful: grouping the students by month gives no row at all for a month with no signups. On the edge data March and July are empty.
What you will learn
- LEFT JOIN: every row of the left table, with NULLs where nothing matched.
- count(column) after an outer join: why count(*) says 1 for nothing.
- Anti-joins: the rows with no match, with IS NULL or NOT EXISTS.
- The WHERE trap: a condition on the right table turns the outer join back into an inner one.
- RIGHT, FULL and CROSS JOIN: the other directions, and every combination.
More in Answering LCO's questions
Build "Courses nobody bought" 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.