✏️ Explanatory Question
Level: Coding Round — A LeetCode favourite; the odd-last-seat edge case is what makes it genuinely tricky.
The Puzzle: A seat table has id (seat number) and student. Swap seats for every pair of adjacent students: 1 with 2, 3 with 4, and so on. If the total number of seats is odd, the last student keeps their seat. Return the result ordered by id.
seat| id | student |
|---|---|
| 1 | Rumman |
| 2 | Krushna |
| 3 | Swetha |
| 4 | Ritesh |
| 5 | Manjula |
| id | student |
|---|---|
| 1 | Krushna |
| 2 | Rumman |
| 3 | Ritesh |
| 4 | Swetha |
| 5 | Manjula |
1↔2 swapped, 3↔4 swapped, and seat 5 (odd last) stays with Manjula.
The key insight — remap the id, not the data: Instead of physically moving students, compute a new id for each seat: odd ids become id + 1 (move to next seat), even ids become id - 1 (move to previous). Then order by that new id. The odd-total edge case is handled by not incrementing the very last odd seat.
SELECT
CASE
-- last seat AND total is odd -> keep the same id
WHEN id = (SELECT MAX(id) FROM seat) AND id % 2 = 1 THEN id
-- odd id -> swap up (take the next seat's position)
WHEN id % 2 = 1 THEN id + 1
-- even id -> swap down (take the previous seat's position)
ELSE id - 1
END AS id,
student
FROM seat
ORDER BY id; The odd-last-seat trap: Without the first WHEN, the last odd seat (id 5) would try to become id 6 — a seat that does not exist — and could disappear or sort incorrectly. The explicit check id = MAX(id) AND id % 2 = 1 keeps that final student in place. This is the entire difficulty of the puzzle.
-- COUNT(*) OVER () gives the total; use it to detect the odd last seat
SELECT
CASE
WHEN id % 2 = 1 AND id = cnt THEN id -- last, odd -> stay
WHEN id % 2 = 1 THEN id + 1 -- odd -> up
ELSE id - 1 -- even -> down
END AS id,
student
FROM (
SELECT id, student, COUNT(*) OVER () AS cnt
FROM seat
) t
ORDER BY id;-- (id + 1) for odd, (id - 1) for even, capped at MAX(id)
SELECT
LEAST(
id + IF(id % 2 = 1, 1, -1),
(SELECT MAX(id) FROM seat)
) AS id,
student
FROM seat
ORDER BY id;
-- LEAST() prevents the last odd seat from exceeding MAX(id) Interviewer follow-up: "What if you had to actually UPDATE the table in place, not just SELECT?" → You would UPDATE seat SET id = CASE ... END, but you must be careful of primary-key collisions mid-update (two rows temporarily wanting the same id). Safer approaches: swap the student values between paired rows instead of the ids, or stage the changes in a temp table then write back. Point out this concurrency/uniqueness concern — it impresses interviewers.