Process Airline Seat Requests
Reported by candidates from Agoda's online assessment. Pattern, common pitfall, and the honest play if you blank under the timer.
The constraint that kills brute force here is order. Agoda's October 2023 OA hands you a seats table and a pile of requests, and the final state depends on processing them by request_id, one after another. A plain join won't do it. This is a SQL question about sequential state, and the trap is treating each request as independent. You need to reason about what each seat looks like after every prior request touches it. If you blank on the stateful logic mid-assessment, StealthCoder runs invisibly as a safety net and reads the problem for you.
The problem
An airline stores the current state of every seat and a sequence of reservation or purchase requests. A request with value 1 reserves a free seat. A request with value 2 purchases a free seat. A person may also purchase a seat that the same person previously reserved. Every other request is ignored. Process requests from the smallest request_id to the largest and return the final state of every seat. Tables seats: seat_no PK (Integer), status (Integer), person_id (Integer) requests: request_id PK (Integer), request (Integer), seat_no (Integer), person_id (Integer) Constraints Every requests.seat_no appears in seats. For this exercise, assume each request_id is unique and return seats in ascending seat_no order.
Reported by candidates. Source: FastPrep
Pattern and pitfall
The trick is that each seat has its own little state machine, and seats don't interact. So you only care about the ordered requests per seat. Status goes free to reserved (request 1 on a free seat), free to purchased (request 2 on a free seat), or reserved to purchased (request 2 by the same person who reserved it). Everything else is ignored, including a reserve on a taken seat and a purchase by a different person. Two ways to solve it: a recursive CTE that walks request_id in order carrying seat state, or window functions per seat_no ordered by request_id that find the first valid transition. The pitfall is ordering by seat_no instead of request_id, or letting a later invalid request overwrite state. Finish with a LEFT JOIN back to seats so untouched seats still appear, ordered by seat_no. StealthCoder is your hedge if the recursive CTE syntax slips on the live OA.
The honest play: practice the pattern, and have StealthCoder ready for the one you didn't see coming.
You can drill Process Airline Seat Requests cold, or you can hedge it. StealthCoder runs invisibly during screen share and surfaces a working solution in under 2 seconds. The proctor sees the IDE. They don't see what's behind it. Built for the candidate who saw this exact problem leak two days before his OA and wondered if anyone had a play.
Get StealthCoderRelated leaked OAs
You've seen the question.
Make sure you actually pass Agoda's OA.
Agoda reuses patterns across OAs. Built for the candidate who saw this exact problem leak two days before his OA and wondered if anyone had a play. Works on HackerRank, CodeSignal, CoderPad, and Karat.
Process Airline Seat Requests FAQ
How hard is the Agoda Process Airline Seat Requests question really?+
Medium to hard for SQL. The individual rules are simple, but they depend on order. Most people stall because plain joins and GROUP BY can't carry state from one request to the next. Once you see it as a per-seat state machine, the query is short.
What's the trick to solving it?+
Think per seat, ordered by request_id. Only three transitions matter: free to reserved, free to purchased, and reserved to purchased by the same person. Everything else is a no-op. A recursive CTE or ordered window logic captures that cleanly.
Do I need a recursive CTE?+
Not strictly, but it's the most direct route. It processes requests in request_id order and carries the seat state forward. You can sometimes get there with window functions like ROW_NUMBER and LAG, but the dependency between steps makes that harder to get right.
What edge cases should I check?+
Reserving a seat someone else already reserved, purchasing a seat reserved by a different person, and purchasing an already purchased seat. All are ignored. Also make sure seats with no requests still show up with their original status and person_id.
How do I prepare in 48 hours?+
Practice recursive CTEs and ordered window functions on small state-machine examples. Write out the transition table on paper first, then translate it to SQL. Check your output against a hand-traced example with three or four requests on one seat.