Postgres in depth · Chapter 19 of 65
The dashboard numbers
By the end of this chapter you can boil a whole table down to the numbers on a dashboard: counts, totals, averages, the smallest and the largest, a list joined into one line, and several filtered counts in one query. 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 opens the LCO dashboard every morning: how many students, how much revenue, how many refunds, how much money is stuck in pending orders. Until now every query you wrote returned one row per row of the table. A dashboard needs the opposite: many rows in, one number out.
Reading all 278 orders and adding them up by hand would work once. Postgres's aggregate functions do it in the query, every morning, on whatever the table holds that day.
What you build: 4 tasks
Each task has tests. You write the code in the lab's editor and press Check.
- Task 1
student-numbers.sql: the student numbers
The dashboard's first card is about students who have not been deleted (deleted_at is empty). Return one row: students (how many), with_phone (how many of them have a phone), cities (how many different cities they live in, not counting a missing city) and referred (how many were referred by another student).
Return four columns: students, with_phone, cities, referred.
- Task 2
sales-numbers.sql: the sales numbers
The sales card, from all orders, in one row: paid_orders (how many orders are paid), revenue (the sum of their totals), average_order (the average total of the paid orders, rounded to 2 decimals) and refunded_orders (how many orders were refunded).
Return four columns: paid_orders, revenue, average_order, refunded_orders.
Careful: one WHERE cannot keep the paid orders for three numbers and the refunded ones for the fourth.
- Task 3
coupon-summary.sql: the coupons in one line
The coupon card shows all codes on one line, biggest discount first (codes with the same discount A to Z), separated by a comma and a space, like LAUNCH50, FREEDOM40, and so on. Return one row: codes (that line), unlimited (how many coupons have no limit on uses, max_uses is empty) and limited_uses (the total of max_uses over the coupons that have a limit).
Return three columns: codes, unlimited, limited_uses.
- Task 4 · interview question
Interview: pending.sql, the money stuck in pending orders
Sana's card shows how much money is waiting in orders that are still pending. Return one row: pending_orders (how many orders are pending) and pending_amount (the sum of their totals, and 0 when there are none).
Return two columns: pending_orders, pending_amount.
Careful: the edge data has no pending orders at all. The dashboard must show 0 there, never NULL.
What you will learn
- Aggregates: count, sum, avg, min, max, and count(DISTINCT ...).
- NULL in aggregates: which ones skip it, and what an empty table returns.
- FILTER: several counts over different rows in one query.
- string_agg: many values joined into one line, in an order you choose.
More in Answering LCO's questions
Build The dashboard numbers 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.