Exchange Seats

Exchange Seats

What is this problem about?

The Exchange Seats coding problem is a SQL task where you are given a table Seat with two columns: id and student. You need to swap the seats of every two adjacent students. If the total number of students is odd, the last student's seat remains unchanged. The output should be ordered by ID.

Why is this asked in interviews?

Companies like Zomato and Amazon use this Database interview pattern to test your ability to use conditional logic and arithmetic in SQL. It evaluates if you can manipulate row indices using CASE statements and modular math. It’s a practical test of how you handle data transformations that depend on neighboring records.

Algorithmic pattern used

This is a Conditional Update/Select problem in SQL.

1. Use a CASE statement to adjust the id for each row:
   • If id is odd and it's not the last row: new id = id + 1.
   • If id is even: new id = id - 1.
   • If id is odd and it is the last row: id remains the same.
2. Select the student name and the new id.
3. Order the final result by the new id.

Example explanation

Seats: (1, Abbot), (2, Bob), (3, Chris), (4, Doris), (5, Emerson)

1. id 1 (odd, not last): becomes 2. Student: Abbot.
2. id 2 (even): becomes 1. Student: Bob.
3. id 3 (odd, not last): becomes 4. Student: Chris.
4. id 4 (even): becomes 3. Student: Doris.
5. id 5 (odd, IS last): becomes 5. Student: Emerson. Sorted result: (1, Bob), (2, Abbot), (3, Doris), (4, Chris), (5, Emerson).

Common mistakes candidates make

• Ignoring the odd total: Forgetting to handle the last student when the count is odd, which often leads to that student "disappearing" or having a duplicate ID.
• Inefficient Joins: Trying to perform a complex self-join when a simple CASE statement on the ID is much more readable and efficient.
• Sorting: Forgetting the ORDER BY id clause at the end.

Interview preparation tip

In SQL, you can get the total count of rows using a subquery: (SELECT COUNT(*) FROM Seat). Use this inside your CASE statement to check if the current odd ID is the last one.