Find all consecutive available seats in a cinema. Two seats are consecutive if their seat_id values differ by 1 and both are available. Return the result ordered by seat_id.
This problem tests your ability to work with self-joins and sequential data—a pattern Apple uses extensively in their reservation and inventory systems. Whether it's finding consecutive time slots for appointments, available parking spaces, or seat assignments, this type of query is fundamental to resource allocation algorithms. The challenge is identifying pairs where both conditions are met simultaneously.
Core concepts: self-join for comparing adjacent rows, ABS() function for difference calculation, DISTINCT to avoid duplicate pairs, and understanding how to express 'consecutive' logic in SQL. The key insight is joining the table to itself where seat IDs differ by exactly 1 and both rows meet the availability criteria.
Apple's systems use similar queries to: find consecutive time slots in Apple Store Genius Bar scheduling, identify available seat groups for group bookings in Apple Music concerts, optimize warehouse bin allocation for consecutive storage, detect gaps in sequential data for quality assurance, and power seat selection UIs in ticketing applications.
When tackling this Apple problem, the key is to understand the grain of the result. Are you returning one row per user, or one row per category? Always start by identifying your unique join keys and consider if filtered aggregations (CASE WHEN) are more efficient than multiple subqueries.
Be careful with NULL values in your JOIN conditions or aggregate functions. In interview scenarios, datasets often include edge cases like zero-count categories or duplicate entries that can throw off a simple COUNT(*) if not handled with DISTINCT.
Share your approach, optimized queries, or ask questions. Learning from others is the fastest way to master SQL.
SELECT DISTINCT c1.seat_id
FROM cinema c1
JOIN cinema c2
ON ABS(c1.seat_id - c2.seat_id) = 1
WHERE c1.available = 1
AND c2.available = 1
ORDER BY c1.seat_id;This self-join approach pairs every available seat with every other available seat that differs by exactly 1 in seat_id. ABS() handles both left and right neighbors in one condition. DISTINCT prevents duplicate seat_ids when a seat is adjacent to multiple available seats. It is intuitive and works on all SQL databases without window function support. The downside is a quadratic join cost on large tables, making it less suitable when the cinema has thousands of rows.
SELECT seat_id
FROM (
SELECT seat_id,
available,
LAG(available) OVER (ORDER BY seat_id) AS prev_avail,
LEAD(available) OVER (ORDER BY seat_id) AS next_avail
FROM cinema
) t
WHERE available = 1
AND (prev_avail = 1 OR next_avail = 1)
ORDER BY seat_id;LAG and LEAD peek at the previous and next rows in seat_id order, eliminating the self-join entirely. The outer WHERE keeps only available seats that have at least one available neighbor. This single-pass approach is more scalable: the optimizer processes the table once, computes window frames, and filters. Prefer this on large datasets or when the table has an index on seat_id. It requires a database that supports window functions (MySQL 8+, PostgreSQL, SQL Server, SQLite 3.25+).
The self-join solution produces a cross-product of available seats before filtering, giving O(n²) complexity in the worst case where most seats are available. On a table with thousands of rows this becomes expensive. An index on (available, seat_id) helps the optimizer quickly locate available seats and probe matching neighbors. The window function approach scans the table once and applies an in-memory frame evaluation, keeping complexity near O(n log n) due to sorting. On modern query engines with a covering index on seat_id the window variant can be 10–50× faster at scale. Both approaches benefit from an index on seat_id for the ORDER BY. The DISTINCT in Solution 1 adds a deduplication step that Solution 2 avoids entirely, saving additional memory and CPU on result sets with many adjacent available blocks.
A frequent mistake is forgetting the DISTINCT in the self-join version: seat A adjacent to seats B and C produces two rows for A, inflating results. Another pitfall is using seat_id - c2.seat_id = 1 instead of ABS(), which only finds right neighbors and misses left neighbors. Candidates sometimes filter only c1.available = 1 and forget c2.available = 1, returning seats adjacent to any seat rather than adjacent available seats. In the window function solution, using PARTITION BY when there is no grouping column causes subtle errors. Edge cases include a single available seat (correctly excluded by both solutions) and gaps in seat_id numbering, which both solutions handle correctly since they compare actual values rather than row positions.
Use a consecutive-grouping technique: assign a group key as seat_id minus ROW_NUMBER() OVER (ORDER BY seat_id) for available seats only. Rows in the same consecutive run share the same key. Then GROUP BY that key and HAVING COUNT(*) >= 3, selecting MIN and MAX seat_id per group. This avoids chaining multiple self-joins and scales cleanly.
That condition only finds cases where c1 is immediately to the right of c2 (c1.seat_id > c2.seat_id). It misses pairs where c1 is to the left. Seat 3 adjacent to seat 4 would match, but seat 4 adjacent to seat 3 would not, causing the result to omit the left-boundary seat of each available pair.
Both queries return an empty result set. The self-join finds no rows satisfying both available filters; the window function query finds no rows where available = 1. Neither throws an error. This is the correct behavior — always verify edge cases like fully booked or fully empty cinemas when testing your solution.