Postgres in depth · Chapter 20 of 65
Revenue per course
By the end of this chapter you can turn a table into a report: one row per group with GROUP BY, groups kept or dropped with HAVING, subtotals and a grand total with ROLLUP and GROUPING SETS, and a pivot table with FILTER. 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
The dashboard's single numbers raised new questions. Kabir asks which courses earn the most. Arjun wants revenue month by month, then the catalogue counted by level and language with subtotals, then a table of order statuses with one row per month and one column per status, the way finance reads it.
Chapter 19's aggregates boil the whole table down to one row. A report needs one row per course, or per month. Running one query per course would mean 24 queries, and the list changes every time a course is added. GROUP BY does it in one.
What you build: 4 tasks
Each task has tests. You write the code in the lab's editor and press Check.
- Task 1
course-revenue.sql: revenue per course
Kabir wants to know which courses earn the most. From the enrollments, return one row per course that earned money: course_id, students (how many enrollments it has) and revenue (the sum of price_paid). Leave out the courses whose revenue is 0, the free ones.
Return three columns: course_id, students, revenue. Order: highest revenue first, then course_id.
The titles come in the next chapter, with joins.
- Task 2
monthly-revenue.sql: revenue month by month
Arjun's monthly report: for the paid orders, one row per month in which they were paid (paid_at): month (the first day of that month, as a date, like 2026-01-01), orders (how many) and revenue (the sum of their totals).
Return three columns: month, orders, revenue. Order: month, earliest first.
- Task 3
catalogue-rollup.sql: the catalogue with subtotals
Arjun wants the catalogue counted by level and language, with a subtotal for each level and a grand total, all in one answer. Count every course, whatever its status.
Return three columns: level, language, courses. A subtotal row has an empty (NULL) language; the grand total has both empty.
Order: level in its own order (beginner, intermediate, advanced), then language A to Z, with each subtotal after its level's rows and the grand total last.
- Task 4 · interview question
Interview: status-pivot.sql, order statuses as a pivot
Finance wants one row per month in which orders were created (created_at), and one column per order status: month (the first day of the month, as a date), then paid, failed, refunded and pending (how many of that month's orders have that status, 0 when none).
Return five columns: month, paid, failed, refunded, pending. Order: month, earliest first.
What you will learn
- GROUP BY: rows sorted into groups, one answer row per group.
- HAVING: a condition on the groups, after they are counted.
- The order a query runs in: why WHERE cannot see a count and HAVING can.
- ROLLUP and GROUPING SETS: subtotals and totals in the same answer.
- Pivots: one column per value, with FILTER.
More in Answering LCO's questions
Build "Revenue per course" 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.