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.
- 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.
- 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.
- 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.
- 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).
- 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.