Joe Celko's SQL for Smarties, 3rd Edition

24.1. Finding Subregions of Size (n)

This example is adapted from SQL and Its Applications (Lorie and Daudenarde 1991). You are given a table of theater seats:

CREATE TABLE Theater
(seat_nbr INTEGER NOT NULL PRIMARY KEY, -- sequencing number
 occupancy_status CHAR(1) NOT NULL             -- values
        CONSTRAINT valid_occupancy_status
        CHECK (occupancy_status IN ('A', 'S'));

In this table, an occupancy_status code of ‘A’ means available, and ‘S’ means sold. Your problem is to write a query that will return the subregions of (n) consecutive seats still available. Assume that consecutive seat_nbrs means that the seats are also consecutive for a moment, ignoring rows of seating where seat_nbr(n) and seat_nbr((n) + 1) might be on different physical theater rows. ...