Postgres in depth · Chapter 33 of 65

Live classes without clashes

By the end of this chapter you can store a span of time as one value, ask whether spans overlap, merge and subtract them, and make Postgres refuse two bookings that clash. 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

On 6 May, Neha Kapoor was due to teach two live classes at once: "TypeScript Deep Dive: office hours" from 19:30 to 21:00 and "React from Zero: hooks clinic" from 20:00 to 21:30. Two rooms of students waited; Sana answered the angry emails. Kabir wants it never to happen again, and Arjun wants to know how many hours each instructor really teaches.

A class is a span, starts_at to ends_at. "Do two spans overlap?" with two columns each is four comparisons that are easy to get wrong at the edges (does a class ending at 20:00 clash with one starting at 20:00?). And no UNIQUE or CHECK can look at other rows to refuse an overlap: a CHECK sees one row, UNIQUE only knows "equal".

What you build: 5 tasks

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

  1. Task 1

    coupon-window.sql: the last minute of Holi

    Sana gets a complaint that HOLI30 was refused at 23:59 on 7 March. List the coupons that were valid at 2026-03-07 23:59 India time (+05:30).

    A coupon is valid from valid_from up to and including valid_until. Return two columns: code and percent_off. Order by code.

  2. Task 2

    clashes.sql: who is booked twice

    Find every pair of live classes of the same instructor whose times overlap. A class that starts exactly when another ends does not clash.

    Return three columns: instructor (the name), first_class and second_class (the titles; first_class is the one that starts first, or the earlier title when they start together). Each pair once. Order by instructor, then first_class.

    The Check adds two classes: one back to back, one that clashes.

  3. Task 3

    teaching-hours.sql: real teaching hours

    Arjun wants each instructor's total live teaching time in hours, counting time when two of their classes overlap only once.

    Return two columns: instructor (the name) and hours (rounded to 2 decimals). Only instructors with live classes. Order by hours, most first, then by instructor.

  4. Task 4

    no-clashes.sql: fix the evening, then forbid clashes

    First move "React from Zero: hooks clinic" to start at 2026-05-06 21:00 and end at 22:30 India time, when Neha's office hours are over.

    Then add an exclusion constraint named live_classes_no_overlap so that no two classes of the same instructor can overlap (back-to-back classes are allowed, and different instructors may teach at the same time).

  5. Task 5 · interview question

    Interview: free-slots.sql, Rahul's free evening

    Kabir wants to add a class on 2026-02-14 between 18:00 and 23:00 India time and asks when Rahul Iyer is free then. Return one row per free span inside that evening.

    Return two columns: free_from and free_until. Order by free_from.

    Careful: on an evening when he has no class, the answer is the whole evening.

What you will learn

  • Range types: tstzrange, int4range, daterange, and what [ and ) mean.
  • Range operators: @> contains, && overlaps, -|- adjacent, * intersection.
  • Multiranges: range_agg merges overlapping spans; - subtracts them.
  • Exclusion constraints: EXCLUDE USING gist, and why btree_gist is needed.

More in The Postgres flavour

Build "Live classes without clashes" 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.