Zomato SQL Interview Question | Using CTEs | Advanced SQL

[@te_a_50_ankitpilankar62]

with cte as (
select *,
case
when order_id%2 = 1 then coalesce(lead(item,1) over (order by order_id),item)
when order_id%2 = 0 then lag(item,1) over (order by order_id)
end as correct_item
from orders
)
select order_id,correct_item from cte

[@user-gg9pm2kv6o]

select
case when order_id % 2 0 and order_id (select count(*) from orders)
then order_id + 1
when order_id % 2 0 and order_id = (select count(*) from orders)
then order_id
else order_id -1 end as corrected_item,
item
from orders
order by corrected_item

[@pravinprince3221]

Thank you for the wonderful video mam

[@prabhatgupta6415]

select * ,case when order_id%2!=0 then lead(item,1,item)over(order by order_id) else lag(item)over(order by order_id) end as k from orders;

[@saranyabalaji7905]

I thought the same . This is straight forward

[@harshboda2922]

SELECT
CASE
WHEN order_id % 2 = 0 THEN order_id - 1
WHEN order_id % 2 = 1 AND order_id < (SELECT MAX(order_id) FROM Orders) THEN order_id + 1
ELSE order_id
END AS corrected_order_id,item
FROM Orders
order by corrected_order_id;

[@HARSHRAJ-wz2rp]

with cte as(
select order_id,item,LAG(item)OVER() AS item_prev,LEAD(item)OVER() as item_next FROM orders
)
select order_id as corrected_order_id,
case
when order_id%2!=0 and item_next is not null THEN item_next
when order_id%2=0 THEN item_prev
ELSE item
END AS item1
FROM cte;

[@devrajpatidar5927]

Hello very good problem statement and nice video
Here is my approach to this problem
select *,
case when order_id %2 =0 then lag(item,1,item) over(order by order_id asc) else
lead(item,1,item) over(order by order_id asc) end as new_item_swap
from zomato_swap;

[@tilu391]

select
case
when order_id%2 =0 and order_id = (select count(*) from orders) then order_id-1
when order_id%2 =1 and order_id = (select count(*) from orders) then order_id
when order_id % 2 =0
then order_id - 1
when order_id % 2 =1
then order_id+1
end as c_item,
item
from orders
order by c_item

[@journey-of-failure]

Ma'am
I need your..
I am suffering in sql question...
How I can better in SQl.. For job ready...
Mai bhut pareshan hoon ma'am...
Jo v mai iskha hoon sql mai... Sb bhool jata hoon jb.. Question dekhta hoon tb

[@vishalmane3139]

practice krte re, ye common problem hai sabki

[@Banker4408]

Yes do practice everyday😊😊😊

[@saqibhussain9555]

WITH cte AS (
SELECT *,
LEAD(item, 1) OVER (ORDER BY order_id) AS lead_,
LAG(item, 1) OVER (ORDER BY order_id) AS lag_
FROM arders
)
SELECT order_id,
case
when order_id %2 0 and lead_ is not null then lead_
when order_id %2 =0 and lag_ is not null then lag_
else item
end as corrected_one
from cte

[@rajasekharreddy4499]

Select order_id as corrected_order_id, case when order_id%2=1 then lead(item, 1,item) over(order by order_ID) when order_id%2=0 then lag(item) over(order by order_ID) end as swamped _item from Orders;

[@hairavyadav6579]

select case when order_id%2=0 then order_id -1 when order_id%2!=0 and order_id = (select max(order_id) from orders) then order_id
when order_id %2!=0 then order_id +1 end as corrected_order_id ,item from orders order by corrected_order_id;

[@chandravideo]

select *,
case when order_id < (select max(order_id) from odd) and order_id % 2 = 1 then
lead(item,1) over (order by order_id)
when order_id % 2 =0 then
lag(item,1) over (order by order_id)
when (select max(order_id) from odd) % 2 = 1 then item
end as Correct_Item
from odd

[@salonideep6]

thank you!

[@hairavyadav6579]

my approach
with cte as (select *,max(order_id) over() num from orders)
select case when order_id%2=0 then order_id -1 when order_id%2!=0 and order_id = num then order_id
when order_id %2!=0 then order_id +1 end as corrected_order_id, item from cte order by corrected_order_id;

[@user-sn8vk9lt4o]

with cte as (
select
*,
case
when order_id = counts then order_id
when order_id % 2 != 0 then order_id + 1 else order_id - 1
end as flag
from orders
cross join (select count(*) as counts from orders) as x
)
select
flag as order_id,
item
from cte
order by flag;

[@Chathur732]

select order_id, case when item_new is null then item else item_new end as item_new
from (
select *,case when (order_id%2=0) then lag(item) over(order by order_id)
when (order_id%2!=0) then lead(item) over(order by order_id) end as item_new
from orders
order by order_id)

[@harshitsalecha221]

WITH Cte1 AS (SELECT *,
CASE WHEN order_id%20 THEN 1 ELSE 0 END flag FROM orders),
cte2 AS (SELECT order_id,
item,sum(flag) OVER(ORDER BY order_id) as final_flag
FROM cte1),
cte3 AS (SELECT *,
COUNT(*) OVER(PARTITION BY final_flag) as count_flag
FROM cte2)
SELECT CASE WHEN order_id%20 THEN order_id+1 ELSE order_id-1 END as order_id,item FROM cte3
WHERE count_flag=2
UNION
SELECT order_id,item
FROM cte3
WHERE count_flag=1
ORDER BY order_id;

[@badrilalnagar9232]

We should not treat others in a way that we would not like for ourselves.

[@AK47-666]

Yea kya hai??

[@bavi5409]

Anyone here from Toronto?
i am looking for someone teach me SQL from scratch.... Money ll be paid