Postgres in depth · Chapter 21 of 65
Who bought what
By the end of this chapter you can combine tables with joins: pair each row with the rows it points at, chain several tables, choose between ON and USING, spot a join that counts things twice, and join a table to itself. 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
Chapters 11 and 12 split LCO's data the right way: a student's name lives in students, an order in orders, what the order bought in order_items, a course's title in courses. Each fact is stored once, and the tables point at each other with ids. That is why the last chapter's revenue report could only show course_id.
Now Sana has a student on the phone asking what she bought, Kabir wants revenue per instructor by name, and Arjun wants to know who referred whom. Every one of these answers lives in two or more tables at once. A join puts them back together for one query, without storing anything twice.
What you build: 4 tasks
Each task has tests. You write the code in the lab's editor and press Check.
- Task 1
diya-purchases.sql: what Diya Patel bought
Sana has Diya Patel on the phone, asking what she bought. List every course in Diya Patel's paid orders (status paid): the course title, price (what Diya paid for that course, from order_items) and paid_at (when the order was paid).
Return three columns: title, price, paid_at. Order: paid_at, earliest first, then title A to Z.
Careful: find Diya by her name, never by an id, and leave out orders that are not paid (on the edge data one was refunded).
- Task 2
instructor-revenue.sql: revenue per instructor, by name
Kabir wants the revenue report with names. For every instructor whose courses have at least one enrollment, return name, courses (how many different courses of theirs have enrollments), students (how many enrollments those courses have) and revenue (the sum of price_paid).
Return four columns: name, courses, students, revenue. Order: highest revenue first, then name A to Z.
Careful: after the joins there is one row per enrollment, so counting course ids counts enrollments unless each course is counted once.
- Task 3
referrals.sql: who referred whom
Arjun is checking the referral programme. For every student who was referred by another student, return student (the student's name) and referred_by (the name of the student who referred them).
Return two columns: student, referred_by. Order: student A to Z.
- Task 4 · interview question
Interview: review-counts.sql, counts without double counting
For every course that has at least one review and at least one enrollment, return title, students (how many students are enrolled), reviews (how many reviews it has) and avg_rating (the average rating of its reviews, rounded to 2 decimals).
Return four columns: title, students, reviews, avg_rating. Order: most reviews first, then title A to Z.
Careful: a course joined to its enrollments and to its reviews in one query pairs every enrollment with every review. The counts come out far too big, with no error.
What you will learn
- Inner join: JOIN ... ON, how rows are paired, and which rows drop out.
- Several joins: a chain from students to the courses they bought; aliases.
- ON and USING: two ways to say which columns match.
- Double counting: why two joins can multiply your counts, and the fix.
- Self joins: a table joined to itself under two names.
More in Answering LCO's questions
Build "Who bought what" 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.