Amazon SQL interview Questions – Leetcode 626 – Exchange Seats

Problem Description –

Mary is a teacher in a middle school and she has a table seat storing students’ names and their corresponding seat ids.

The column id is a continuous increment.

Mary wants to change seats for the adjacent students.

Can you write a SQL query to output the result for Mary?

For the sample input, the output is:

Note:

If the number of students is odd, there is no need to change the last one’s seat.

Difficulty Level – Medium

Solution –

SELECT 
	CASE WHEN MOD(id, 2) != 0 AND counts != id THEN id + 1 -- for odd ids
		 WHEN MOD(id, 2) != 0 AND counts = id THEN id -- special case for last seat
		 ELSE id - 1 -- For even ids
		 END as id,
	student
FROM 
seat, (SELECT COUNT(*) as counts 
	   FROM seat) AS seat_count
ORDER by id 

To solve this problem, first, we are calculating the number of seats using the COUNT() function. Then We are using 3 CASE statements, 2 for the odd ids and one for the even ids, two for odd ids as we have a special case. The first CASE statement says that when the id is odd MOD(id, 2) != 0 and counts != id means when odd ids are other than 5 in this case 1 and 3 then add 1 to the ids. This will exchange the seat of a person with id 1 to 2 and 2 to 4. The second CASE statement is for the last odd row, which we do not need to change, so we kept the id as it is. And for the third CASE which is for the even rows, we subtracted the ids by 1, so the person with id 2 becomes 1 and the person with id 4 becomes 3.