Exchange Seats | Leetcode 626 | Crack SQL Interviews in 50 Qs

1. Problem Statement & Objectives

In this chapter, we explore LeetCode 626: Exchange Seats, a classic SQL interview problem that tests conditional logic, row manipulation, and mathematical transformations within relational databases.

The primary objective is to swap the seat id of every two consecutive students in a given table. If the total number of students is odd, the id of the last (orphan) student must remain unchanged. Finally, the result table must be ordered by id in ascending order.

2. Database Schema & Input Format

We are given a single table named seat.

• Columns: id (integer) and student (varchar).
• Primary Key: id (guaranteed continuous incrementing sequence: 1, 2, 3, 4, ...).

Input Example (seat table)

3. Core Mental Model & Real-Life Analogy

Real-Life Analogy: The Auditorium Seating Swap

Imagine an auditorium where students are seated in a single straight line in numbered chairs from 1 to N.

• The teacher asks adjacent pairs to swap seats with each other (Person in seat 1 swaps with person in seat 2; person in seat 3 swaps with person in seat 4, and so on).
• If there is an odd total number of students, the very last student sitting in the final odd chair looks around, realizes there is no pair partner for them, and quietly stays in their original seat.

To implement this programmatic transformation in SQL, we cannot physically move physical rows around without a conditional expression. We must dynamically calculate what id each student should possess in the final output using a CASE statement.

4. Analytical Breakdown: The Three Logical Cases

When analyzing any given row with an integer id, we encounter three distinct scenarios:

1. Odd IDs that are NOT the last row (e.g., 1, 3):
   • These need to shift forward by 1 position (id + 1). Abbot (id = 1) takes Doris's place (id = 2).
2. Even IDs (e.g., 2, 4):
   • These need to shift backward by 1 position (id - 1). Doris (id = 2) takes Abbot's place (id = 1).
3. The Last ID when total rows are Odd (e.g., 5):
   • This row must remain unchanged (id). Jeams (id = 5) stays right where he is.

5. SQL Implementation Strategy: The CASE Expression

SQL lacks procedural if-else blocks in standard SELECT queries, but it provides the powerful CASE expression. It acts exactly like a programmatic switch-case or if-else ladder.

Step-by-Step Code Construction

Let us build the query piece by piece.

SELECT 
    CASE 
        -- Condition 1: Check if it is the last row AND the total count is odd
        WHEN id = (SELECT MAX(id) FROM seat) AND MOD(id, 2) = 1 THEN id

        -- Condition 2: If the ID is odd (and not the last one), increment it
        WHEN MOD(id, 2) = 1 THEN id + 1

        -- Condition 3: Otherwise (it must be even), decrement it
        ELSE id - 1
    END AS id,
    student
FROM seat
ORDER BY id ASC;

6. Detailed Walkthrough of Query Execution

Let's trace how our SQL query evaluates each row from our example input:

7. Complete Final Output Table

After sorting by the newly evaluated id in ascending order, the database returns:

8. Summary and Key Takeaways

• CASE Expressions in SQL: Essential for handling row-by-row conditional transformations and multi-branch logic inside a SELECT query.
• Modulo Operator (MOD(id, 2)): The standard database function used to distinguish between even (0) and odd (1) integer sequences.
• Handling Edge Cases (Odd Counts): Subqueries like (SELECT MAX(id) FROM seat) allow us to safely identify boundary rows (such as the final orphan student) so they bypass swapping rules.
• Declarative Order: Always ensure explicit sorting using ORDER BY id ASC to guarantee deterministic output positioning matching the problem constraints.
• Readability & Aliasing: Using END AS id properly closes the conditional evaluation block and assigns the resulting computed value back to the id column stream.