Postgres in depth · Chapter 16 of 65
Calculated columns
By the end of this chapter you can calculate new columns from the ones a table already has: arithmetic, labels with CASE, conversions between types with casts, and rounding that gives the right rupee. 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
LCO's data is in (run lab load if your database is still empty). Now the questions start. Kabir is adding GST to the checkout page and wants every price with its tax. Sana needs to tell students what a coupon really saves them. Arjun wants courses sorted into price bands for the catalogue.
None of those numbers is stored anywhere. The tables hold price, percent_off and duration_seconds; everything else must be worked out in the query, row by row. Storing a tax column would be a mistake: change the rate and every row is wrong. Calculate it when you ask.
What you build: 4 tasks
Each task has tests. You write the code in the lab's editor and press Check.
- Task 1
gst.sql: GST on every paid course
Kabir's checkout page adds 18% GST to the course price. For every published course that is not free, return its title, price, gst (18% of the price, rounded to 2 decimals) and total (price plus gst).
Return four columns: title, price, gst, total. Order: most expensive first, then title A to Z.
Drafts are not on sale, and free courses pay no tax: leave both out.
- Task 2
coupon-savings.sql: what each coupon saves
Sana wants to tell students what each coupon takes off React from Zero, a 2,499 rupee course. For every coupon, return its code, percent_off, saving (2499 times the percentage, in rupees, rounded to 2 decimals) and you_pay (2499 minus the saving).
Return four columns: code, percent_off, saving, you_pay. Order: biggest saving first, then code A to Z.
Careful: percent_off is an integer. Divide it by 100 the obvious way and every saving comes out as zero.
- Task 3
price-bands.sql: the catalogue's price bands
Arjun wants every course (any status) in a price band: free (price 0), budget (below 1500), standard (1500 up to, but not including, 3000) and premium (3000 or more).
Return three columns: title, price, band (one of those four words). Order: cheapest first, then title A to Z.
- Task 4 · interview question
Interview: lesson-length.sql, seconds into minutes
The lesson page shows long lessons as 23 min 12 s, and the course page as 23.20 minutes. For every lesson of at least 23 minutes (1380 seconds or more), return title, duration_seconds, minutes (the whole minutes), seconds (the seconds left over after the whole minutes, 0 to 59) and exact_minutes (the length in minutes as a decimal, rounded to 2 places).
Return five columns: title, duration_seconds, minutes, seconds, exact_minutes. Order: longest first, then title A to Z.
Careful: the same division must drop the decimals for minutes and keep them for exact_minutes.
What you will learn
- Expressions: arithmetic on columns, one result per row.
- Integer division: why 3 / 4 is 0, and how to get 0.75.
- Types and casts: integer, numeric and double precision; :: and CAST.
- Rounding: round, trunc, ceil, floor, and the round that does not exist.
- CASE: a label chosen by conditions, the first one that is true.
More in Answering LCO's questions
Build "Calculated columns" 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.