LeetCode Medium 626 Amazon Interview SQL Question with Detailed Explanation

[@dantenekomew]

Best approach, getting previous and next is easier to understand than, let's say id+1 and id-1

[@EverydayDataScience]

Glad that you found the video useful 😊

[@adityams1659]

*I figured out a more efficient and elgegant solution to this*
SELECT
CASE WHEN (select max(id) from Seat)%2 = 1 and id = (SELECT max(id) FROM Seat) THEN id
WHEN id%2=1 THEN id+1
WHEN id%2=0 THEN id-1 END AS id,
student
FROM Seat
ORDER BY id

[@anjanijanyavula373]

I too the got the same way

[@raghavendrac4710]

Your videos are very helpful. Thanks

[@bhanukoushikthyarla2682]

Good Explanation Sir !

[@snehalpawar9995]

The approach is really good 👍

[@EverydayDataScience]

Glad that you found the video useful, Snehal.

[@pramodplays8032]

hi !! Great video . I just want to ask how to develop logic building skills for SQL Queries

[@learner817]

Nice approach and well explained. Thank you!

[@EverydayDataScience]

Glad that you found the videos useful.

[@abhijeethvs]

with cte as (
select student,
case when id%2 = 1 then id+1 else id-1 end as new_id
from seat
order by new_id)
select ROW_NUMBER() over()id ,student from cte

[@expandingourselves]

with cte1 as(
select case when id = (select max(id) from Seat) then id
when id%2 = 0 then id-1
when id%2 = 1 then id+1 else 'null' end as id , student
from Seat
where (select max(id) from Seat)%2 = 1
order by id asc) , cte2 as
(
select case when id%2 = 0 then id-1
when id%2 = 1 then id+1 else 'null' end as id , student
from Seat
where (select max(id) from Seat)%2 = 0
order by id asc )
select * from cte1
union all
select * from cte2

[@muddassirnazar9275]

Simple way:/* Write your T-SQL query statement below */
with cte as
(select *,
lag(student) over(order by id) as prev,
lead(student) over(order by id) as nextt
from seat)
select id,case when id%2=0 then prev
when id%2!=0 and nextt is not null then nextt
else student end as student
from cte

[@kirtanshah9192]

with cte as (
select id, student as original_student, case when id % 2 = 1 then lead(student,1) over(order by id) else
lag(student,1) over(order by id) end as student
from seat)
select id, case when student is null then original_student else student end
as student
from cte

[@sahilnagar1767]

How about this?
SELECT
id,
CASE
WHEN id % 2 = 1 THEN LEAD(student,1, student) OVER (ORDER BY id)
WHEN id % 2 = 0 THEN LAG(student) OVER (ORDER BY id)
END AS student
FROM
Seat;

[@mickyman753]

how are you handling the last column case where the id can be odd?

[@ujjwalvarshney3188]

select a.id , CASE WHEN B.STUDENT IS NULL THEN a.STUDENT
WHEN a.id%2 =0 THEN c.STUDENT else b.student end as student
From Seat a left join Seat b
on a.id+1 = b.id
left join Seat c
on a.id = c.id + 1

[@mickyman753]

with cte1 as (
select id,student,lead(id) over(order by id) as next,
lag(id) over(order by id) as prev
from seat),
cte2 AS
( SELECT id, student,
CASE
WHEN id = (SELECT COUNT(*) FROM seat) THEN
CASE
WHEN id % 2 != 0 THEN id
ELSE prev
END
ELSE
CASE
WHEN id % 2 = 0 THEN prev
ELSE next
END
END AS swap_id FROM cte1 )
select swap_id as id,student from cte2 order by id;

[@pratikjugantmohapatra18]

WITH cte AS (
SELECT id, student,
LAG(student) OVER (ORDER BY id) AS prev_name,
LEAD(student) OVER (ORDER BY id) AS next_name
FROM Seat
)
SELECT id,
CASE
WHEN id % 2 = 1 AND next_name IS NOT NULL THEN next_name
WHEN id % 2 = 0 THEN prev_name
ELSE student
END AS student
FROM cte
ORDER BY id;