Writing the queries the application needs

A booking system spends most of its life reading: showing what is on, how full each show is, searching for a film, reporting revenue. This lesson writes those queries against the built database, applying the query lessons (modules 3, 5, 7) to a real workload. Every result shown was produced by the running system.

Seat availability — the most-run query

The seat-picking page needs "how many seats are left for this show". It is total_seats minus the number booked — a LEFT JOIN and a count:

SELECT sc.total_seats,
       count(bs.seat_id) AS booked,
       sc.total_seats - count(bs.seat_id) AS available
FROM shows sh
JOIN screens sc ON sc.id = sh.screen_id
LEFT JOIN booking_seats bs ON bs.show_id = sh.id
WHERE sh.id = 1
GROUP BY sc.total_seats;

 total_seats | booked | available
-------------+--------+-----------
         100 |      2 |        98

The LEFT JOIN matters (module 5): a show with no bookings must still return a row saying "available = total", and an inner join would drop it entirely — the empty-result trap. This query runs on every page load for the show, which is exactly why the next lesson caches it.

For which specific seats are free (to draw the seat map), the shape is an anti-join — seats for the screen that are not in booking_seats for this show:

SELECT s.row_label, s.seat_num
FROM seats s
WHERE s.screen_id = (SELECT screen_id FROM shows WHERE id = 1)
  AND NOT EXISTS (
    SELECT 1 FROM booking_seats bs WHERE bs.show_id = 1 AND bs.seat_id = s.id
  )
ORDER BY s.row_label, s.seat_num;

NOT EXISTS is the right tool here — safe with the seat ids and clear in intent — rather than NOT IN, which has the NULL trap from module 2.

The showtime listing — and avoiding the N+1

"What's on, with screen and seats-left" is the home page. The naive approach — fetch shows, then loop and query seats-left per show — is the N+1 problem from module 7. One query does it instead, joining and aggregating:

SELECT m.title, sc.name AS screen, sh.starts_at,
       round(sh.price_paise / 100.0, 2) AS price_rupees,
       sc.total_seats - count(bs.seat_id) AS seats_left
FROM shows sh
JOIN movies m ON m.id = sh.movie_id
JOIN screens sc ON sc.id = sh.screen_id
LEFT JOIN booking_seats bs ON bs.show_id = sh.id
GROUP BY m.title, sc.name, sc.total_seats, sh.starts_at, sh.price_paise
ORDER BY sh.starts_at;

       title       |     screen      |       starts_at        | price_rupees | seats_left
-------------------+-----------------+------------------------+--------------+------------
 Laapataa Ladies   | Screen 1        | 2026-09-28 12:30:00+00 |       250.00 |         98
 Kantara           | Screen 3 (IMAX) | 2026-09-28 15:30:00+00 |       400.00 |        150
 The Kashmir Files | Screen 2        | 2026-09-29 09:30:00+00 |       200.00 |         80
 Ponniyin Selvan   | Screen 3 (IMAX) | 2026-09-29 13:00:00+00 |       400.00 |        150
 Laapataa Ladies   | Screen 1        | 2026-09-29 15:30:00+00 |       300.00 |        100

The whole listing — five shows, three joins, an aggregate, ordered by time — in one query, one round trip. Note the two things module 8 warned about, done right: sc.total_seats had to be in the GROUP BY (leaving it out is an error — every non-aggregated selected column must be grouped, module 3), and round(price_paise / 100.0, 2) formats the money so it reads 250.00, not 250.0000000000.

Searching for a movie

The brief wants title search. PostgreSQL full-text (module 12), not LIKE:

SELECT title FROM movies
WHERE to_tsvector('english', title) @@ plainto_tsquery('english', 'ladies');

      title
-----------------
 Laapataa Ladies

At five movies a sequential scan is instant, but the same query with a GIN index on to_tsvector('english', title) scales to a catalogue of thousands — and it stems and ranks, which LIKE '%ladies%' cannot. For the small movie table this is arguably over-engineering; it is here to show the pattern you would use at scale.

Revenue per movie — the report

The business wants revenue per film. A join from bookings up to movies, filtered to confirmed bookings, grouped and summed (module 3):

SELECT m.title,
       count(DISTINCT b.id) AS bookings,
       round(sum(b.amount_paise) / 100.0, 2) AS revenue_rupees
FROM bookings b
JOIN shows sh ON sh.id = b.show_id
JOIN movies m ON m.id = sh.movie_id
WHERE b.status = 'confirmed'
GROUP BY m.title
ORDER BY revenue_rupees DESC;

      title      | bookings | revenue_rupees
-----------------+----------+----------------
 Laapataa Ladies |        1 |         500.00

Two deliberate choices. WHERE b.status = 'confirmed' — cancelled bookings do not count as revenue, the business rule made explicit in the query. And count(DISTINCT b.id) — because a booking covers several seats, joining to seats would multiply the booking row (the fan-out from module 5); counting distinct booking ids gives the true number of bookings, not seats. Getting that DISTINCT wrong is a classic reporting bug that silently inflates the numbers.

How full is each show — occupancy

Combining the ideas, a manager's occupancy report:

SELECT m.title, sh.starts_at,
       count(bs.seat_id) AS sold,
       sc.total_seats,
       round(100.0 * count(bs.seat_id) / sc.total_seats, 1) AS percent_full
FROM shows sh
JOIN movies m ON m.id = sh.movie_id
JOIN screens sc ON sc.id = sh.screen_id
LEFT JOIN booking_seats bs ON bs.show_id = sh.id
GROUP BY m.title, sh.starts_at, sc.total_seats
ORDER BY percent_full DESC;

Again a LEFT JOIN so empty shows report 0% rather than vanishing, and 100.0 * (not 100 *) to force decimal division — 100 / 100 is 1 in integer arithmetic, a subtle bug that would report every show as either 0% or a whole number.

What these queries share

• LEFT JOIN wherever a "zero" must still appear — empty shows, unbooked seats. The single most common reporting mistake is an inner join that silently drops the zeros.
• count(DISTINCT ...) when a join fans out — counting bookings through a seat join without DISTINCT inflates the count.
• Decimal division and round(...) for money and percentages — integer division is a silent wrong answer.
• One query, not N+1 — joins and aggregates do in one round trip what a loop would do in hundreds.
• WHERE encodes the business rule — "confirmed only" is not incidental; it is the definition of revenue.

Each is a lesson from earlier in the course, now load-bearing in a real application.

Check your work

Why the availability query uses a LEFT JOIN. A show with no bookings must still return a row (available = total); an inner join drops it.

The right tool for "which seats are free". NOT EXISTS (an anti-join) — not NOT IN, which has the NULL trap.

How the showtime listing avoids N+1. One query with joins and an aggregate instead of a loop of per-show queries.

Why sc.total_seats must be in the GROUP BY. Every non-aggregated selected column must be grouped (module 3).

Why search uses full-text, not LIKE. Stemming and ranking, and it scales with a GIN index — LIKE cannot.

Why revenue filters status = 'confirmed'. Cancelled bookings are not revenue — the business rule in the query.

Why count(DISTINCT b.id) in the revenue query. A booking covers several seats; joining fans out the booking row, so a plain count inflates it.

Why 100.0 * in the occupancy percentage. Integer division (100 * with integer operands) truncates; forcing decimal gives the real percentage.

The five things the queries share. LEFT JOIN for zeros, count(DISTINCT) for fan-out, decimal division for money/percent, one-query-not-N+1, and WHERE encoding the business rule.

Practice

1. Run the availability query for a show with no bookings and confirm it returns available = total (not an empty result). Then change the LEFT JOIN to an inner join and watch the row vanish.
2. Write the free-seats query with NOT EXISTS, then with NOT IN, and construct the case where NOT IN misbehaves.
3. Run the showtime listing. Then write the N+1 version (fetch shows, loop) and count the queries.
4. Remove sc.total_seats from the GROUP BY and read the error. Explain it.
5. Search for a movie with full-text, then with LIKE, on a title where stemming matters.
6. Run the revenue query. Then remove DISTINCT from the count and, after booking multiple seats, watch the booking count inflate.
7. Cancel a booking (status = 'cancelled') and confirm revenue drops.
8. Write the occupancy query with 100 * instead of 100.0 * and observe the wrong percentages.
9. EXPLAIN the availability query and confirm the UNIQUE (show_id, seat_id) index is used.

Official documentation

• PostgreSQL — Joins (including LEFT JOIN) — Keeping the zero rows.
• PostgreSQL — Aggregate functions and GROUP BY — count, sum, and the grouping rules.
• PostgreSQL — Subquery expressions (EXISTS, IN) — NOT EXISTS versus NOT IN.
• PostgreSQL — Full text search — The movie search.
• PostgreSQL — Using EXPLAIN — Confirming the indexes are used.

Next: adding the cache, and proving it helped.