Exchange seats
SQL data engineering interview problem. Difficulty: intermediate. Pattern: Window Functions. About 14 minutes. Part of the Pro drill bank.
seat_assignments contains student seat numbers with columns id and student, where id is a continuous sequence starting at 1. Swap the seat of every two consecutive students (seat 1 swaps with seat 2, seat 3 with seat 4, etc.). If the total number of students is odd, the last student's seat remains unchanged. Return the result ordered by id ascending.
Requirements
- Output columns: id, student.
- Order by id ascending.
Constraints
- id is a continuous sequence 1, 2, 3, ...
Examples
Input: seat_assignments id,student 1,Abbot 2,Doris 3,Emerson 4,Green 5,Jeames Output: id | student 1 | Doris 2 | Abbot 3 | Green 4 | Emerson 5 | Jeames Why this passes: Seats are swapped in pairs (1<->2, 3<->4), and the last seat 5 stays put because the total count is odd.
Input: seat_assignments id | student 1 | Ann 2 | Bob Output: id | student 1 | Bob 2 | Ann Why this passes: Even count of 2 swaps cleanly.
Topics: lakebench, sql, lead-lag, parity.
More SQL interview questions · All interview problems · Learn data engineering
Exchange seats
Interview-style drill: Swap student names between consecutive odd and even seat positions, leaving the final student in place if count is odd.
`seat_assignments` contains student seat numbers with columns `id` and `student`, where `id` is a continuous sequence starting at 1. Swap the seat of every two consecutive students (seat 1 swaps with seat 2, seat 3 with seat 4, etc.). If the total number of students is odd, the last student's seat remains unchanged. Return the result ordered by `id` ascending.