Postgres in depth · Chapter 29 of 65

Sale day

By the end of this chapter you can write changes that are safe to run twice, insert-or-update rows in one statement, and change one table using the rows of another. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.

  • Part: The Postgres flavour
  • 5 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 running LCO's first big sale on Sunday: new coupons, a sale price list, and a free course for every paying student. Sana has a worry from the last sale: the payment gateway sends its "payment captured" message (a webhook) again whenever LCO is slow to answer, and twice it was recorded twice. Priya wants every sale-day script to be idempotent: running it a second time must change nothing and fail nothing, because scripts get retried.

What you know so far cannot do it. A plain INSERT of a row that already exists fails with a duplicate key error, and it fails the whole statement, not just that row. An UPDATE with a subquery per column gets long and slow when the new values come from another table. And "update this order, record a payment, remove an enrollment" as three statements can stop halfway.

What you build: 5 tasks

Each task has tests. You write the code in the lab's editor and press Check.

  1. Task 1

    gift.sql: a free course for every paying student

    Kabir gifts HTML and CSS from Zero (slug html-css-from-zero) to every student who has at least one paid order and is not deleted (deleted_at is NULL).

    Each new enrollment has enrolled_at 2026-09-15 10:00 India time (+05:30) and price_paid 0. Students who already have the course keep their enrollment exactly as it is.

    The Check runs your file twice: the second run must not fail and must change nothing.

  2. Task 2

    coupons.sql: the sale coupons, new or updated

    Load Kabir's coupon list. For each code: a new code is inserted; an existing code gets the new percent_off, max_uses, valid_from and valid_until, and keeps its id and its used_count.

    DIWALI25: 25 percent off, 200 uses, valid 2026-11-01 00:00 to 2026-11-05 23:59. STUDENT10: 15 percent off, no limit (max_uses NULL), valid 2026-02-01 10:00 to 2027-03-31 23:59. FREEDOM40: 40 percent off, 150 uses, valid 2026-08-13 10:00 to 2026-08-17 23:59. All times are India time (+05:30).

    The Check runs your file twice.

  3. Task 3

    prices.sql: the sale price list

    Set the sale prices by slug: react-from-zero 1499, nodejs-apis 1499, postgres-for-developers 1999, devops-with-aws 2999, vue-basics 999.

    vue-basics is not a course yet, so it changes nothing. Every other course keeps its price.

  4. Task 4

    cleanup.sql: what deleted students leave behind

    Students who deleted their account keep a row in students (deleted_at is set). Delete their lesson_progress rows and their reviews; keep the students themselves, and everybody else's rows.

  5. Task 5 · interview question

    Interview: refund.sql, a refund in one statement

    Sana refunds the paid order Aarav Shah placed most recently (latest created_at). In one statement:

    the order's status becomes refunded; a payment is recorded for it with gateway_ref 'rfnd_' followed by the order's id, amount equal to the order's total, status refunded, created_at 2026-09-20 12:00 India time; and Aarav's enrollments in the courses of that order are deleted.

    Careful: the parts of one statement do not see each other's changes.

What you will learn

  • ON CONFLICT: insert, and say what to do when the row is already there.
  • MERGE: insert, update or delete, case by case, in one statement.
  • UPDATE ... FROM and DELETE ... USING: change rows by joining another table.
  • Writable CTEs: several changes in one statement, passing rows along with RETURNING.

More in The Postgres flavour

Build "Sale day" 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.