626 Exchange Seats

Description

Table: Seat

• id is the primary key (unique value) column for this table.
• Each row of this table indicates the name and the ID of a student.
• The ID sequence always starts from 1 and increments continuously.

Problem Statement

Write a solution to swap the seat id of every two consecutive students. If the number of students is odd, the id of the last student is not swapped. Return the result table ordered by id in ascending order. The result format is in the following example.

Example 1:

Input:

• Seat table:

Output:

Explanation:

• Note that if the number of students is odd, there is no need to change the last one’s seat.

Solution

The problem is essentially asking us to swap the seat id of every two consecutive students. To get the consecutive students, you can use LEAD and LAG functions.

LAG(student, 1) OVER (ORDER BY id) will give you the previous student and LEAD(student, 1) OVER (ORDER BY id) will give you the next student. There is one caveat though. What happens when we reach the last record which is odd number? In that case, we don’t have the next student. So, we will get null value there. The problem asks us to not swap for this case. This is where we can use the default value in the LEAD function like LEAD(student, 1, student) OVER (ORDER BY id).

With these ideas, you can write the following query.

1. Find the previous and next student.
2. If the id is odd, then use the next_student else use the prev_student.

 1WITH student_prev_next AS(
 2    SELECT id, student,
 3        LEAD(student, 1, student) OVER (ORDER BY id) AS next_student,
 4        LAG(student, 1) OVER (ORDER BY id) AS prev_student
 5        FROM Seat
 6) SELECT id,
 7    CASE 
 8        WHEN MOD(id, 2) = 1 THEN next_student
 9        ELSE prev_student
10        END AS student
11    FROM student_prev_next;