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.