SQL for Consecutive Theater Seats | Dataford Interview Questions

The Zulily app needs to recommend the best available seating block for a live shopping theater event. Write a PostgreSQL query that finds one block of consecutive available seats for a specified screening.

Requirements

Consider only screening 101 and seats whose status is available.
Treat seats as consecutive only within the same row and seat rank, where rank 1 is the best rank.
Select the block with the best rank first, then the greatest number of consecutive seats, then the earliest row and starting seat as deterministic tie-breakers.
Return the screening name, row, rank, starting seat, ending seat, and block length.

Schema

screenings

Column	Type	Description
screening_idPK	INT	Unique screening identifier
screening_name	VARCHAR(100)	Name of the Zulily event or show

seat_inventory

Column	Type	Description
seat_idPK	INT	Unique seat inventory identifier
screening_id	INT	References screenings.screening_id
row_label	VARCHAR(10)	Theater row label
seat_number	INT	Numeric position within the row
seat_rank	INT	Seat quality rank, with 1 as the best rank
status	VARCHAR(20)	Seat availability status

Your question is SQL for Consecutive Theater Seats. Start with the requirements and the two tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem