LeetCode Medium 626 Amazon Interview SQL Question with Detailed Explanation

In this video I solve and explain a medium difficulty leetcode SQL question using MySQL query. This question has been asked in Apple, Facebook, Amazon, Google, Adobe, Microsoft, Adobe interviews or what we popularly call FAANG interviews.
I explain the related concept as well. This question includes points to keep in mind to develop SQL queries.

LeetCode is the best platform to help you enhance your skills, expand your knowledge and prepare for technical interviews.

If you found this helpful, Like and Subscribe to the channel for more content.

#LeetCodeSQL #FAANG #SQLinterviewQuestions

[dantenekomew]

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

[adityams]

*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

[learner]

Nice approach and well explained. Thank you!

[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

[pramodplays]

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

[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

[muddassirnazar]

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

[sahilnagar]

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;

[kirtanshah]

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

[ujjwalvarshney]

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

[pratikjugantmohapatra]

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;