Swap Neighbouring Seats in the Exam Hall — SQL Medium Problem

Problem statement

To discourage copying, the invigilator swaps every pair of neighbouring candidates in the hall: the candidates in seats 1 and 2 trade places, then seats 3 and 4, and so on. When the number of seats is odd, the candidate in the last seat stays where they are. Seats are numbered 1, 2, 3, … with no gaps.

Return every seat after the swap, with columns seat_no and student_name, ordered by seat_no.

Tables

Table: HallSeat

Primary key: seat_no.

One row per occupied seat; the seat numbers run from 1 to the number of rows without gaps. student_name is never NULL.

Examples

Example 1

HallSeat

Output

Example 2

HallSeat

Output

How to solve Swap Neighbouring Seats in the Exam Hall

There are two ways to look at a swap: the names move between fixed seats, or each candidate gets a new seat number. The second is a single CASE over each row. A candidate in an even seat moves to seat_no - 1. A candidate in an odd seat moves to seat_no + 1 — unless their seat is the last one, which only happens when the count is odd; then they stay. The count is a scalar subquery, (SELECT COUNT(*) FROM HallSeat), evaluated once.

The CASE branches are checked in order, so testing evenness first means the "last seat" branch is reached only for odd seats: an even last seat still swaps down, which is right. Ordering by the new seat_no (the alias, which ORDER BY prefers over the column) lays the hall out again.

The other view keeps seat numbers and fetches the neighbour's name: a self LEFT JOIN to the partner seat (seat_no + 1 for odd, - 1 for even), falling back to the candidate's own name when there is no partner. Window functions do the same without a join — LEAD for odd seats, LAG for even ones, with COALESCE covering the lonely last seat. All three read the table once (plus an index lookup per row for the join).

Reference solution (MySQL)

SELECT CASE
         WHEN MOD(seat_no, 2) = 0 THEN seat_no - 1
         WHEN seat_no = (SELECT COUNT(*) FROM HallSeat) THEN seat_no
         ELSE seat_no + 1
       END AS seat_no,
       student_name
FROM HallSeat
ORDER BY seat_no

Another way

SELECT s.seat_no, COALESCE(p.student_name, s.student_name) AS student_name
FROM HallSeat s
LEFT JOIN HallSeat p ON p.seat_no = IF(MOD(s.seat_no, 2) = 1, s.seat_no + 1, s.seat_no - 1)
ORDER BY s.seat_no

Another way

SELECT seat_no,
       CASE WHEN MOD(seat_no, 2) = 1
            THEN COALESCE(LEAD(student_name) OVER (ORDER BY seat_no), student_name)
            ELSE LAG(student_name) OVER (ORDER BY seat_no)
       END AS student_name
FROM HallSeat
ORDER BY seat_no