Zomato SQL Interview Question | Using CTEs | Advanced SQL

[@PavankalyanSreesailam]

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

[@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

[@astasingh]

nice video here is my approach :-
with cte as(
select order_id,item,lead(item,1) over(order by order_id) next,
lag(item) over(order by order_id)prev from orders
)

select order_id, case when order_id%2=0 then prev when order_id%20 then ifnull(next,item) end as item from cte

[@ishitvasingh9902]

Nicely explained video.
I have an approach with more advanced funtions i.e, lead and lag,
SELECT order_id,
CASE
WHEN order_id % 2 = 1 THEN COALESCE(lead(item) over (ORDER BY order_id), item)
ELSE lag(items) over (ORDER BY order_id)
END AS item
FROM orders

[@NaveenKumar-fm5yg]

just nowi think about this approach brother

[@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;

[@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

[@MasoodKazi-sx6el]

thank you amazing explanation

[@pravinprince3221]

Thank you for the wonderful video mam

[@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

[@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;

[@JEETSHAW-e5w]

can you expln why can't we use MAX(order_id) directly, why it is necessary to use subquery?
If aggregated functions can't be used here then how you decide where and where not can we put aggregated functions, rules regarding placement of agg funcn is too confusing?

[@SamarjitParida-u9r]

select *,case when id%2!=0 then (lead(item,1,item) over (order by id))
else (lag(item,1,item) over (order by id)) end as [Correct Order]
from orders

[@SnapMathShorts]

Using window function :
select * ,
case when order_id%2=1 then lead(item,1,item) over(order by order_id)
else lag(item,1) over(order by order_id) end as new_item
from orders;
Using left join :
with cte as (
select o.order_id as oid1, o.item as item1, o2.order_id as oid2, o2.item as item2
from orders o
left join orders o2 on o2.order_id = o.order_id + 1)
select oid1 as order_id, coalesce(item2,item1) as item
from cte
where oid1 % 2=1
union all
select oid2 ,item1
from cte
where oid2 % 2=0
order by 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;

[@humdrum2041]

select
(case
when order_id%20 and order_id in(select max(order_id) from orders) then order_id
when order_id%20 then order_id+1
when order_id%2=0 then order_id-1
else 'hi'
end) id,item
from orders
order by id;

[@kingsleyarisah6354]

works but won't work if there were 8 items in list

[@vinil9212]

change varchar 255 to in 25 in case your output is weird: with cte as (
select order_id,
lead(item,1,item) OVER(order by order_id) as lead_item,
lag(item) OVER(order by order_id) as lag_item
from orders)
select order_id, case
when order_id%2=0 then lag_item else lead_item end as item from cte;

[@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

[@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;

[@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;

[@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

[@JeevanC-l3k]

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;

[@badrilalnagar9232]

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

[@AK47-666]

Yea kya hai??

[@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)

[@journey-of-learn]

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

[@Radha4408]

Yes do practice everyday😊😊😊

[@shadyelsherif8688]

By practice and practice and practice
Daily practice as much as you can
Good luck

[@data.eng.telugu]

WITH CTE AS (
select
*,
lead(order_id,1,order_id) over (order by order_id) as l1,
lag(order_id,1) over (order by order_id) as l2
from
orders
)
SELECT
CASE WHEN order_id % 2 = 0 then l2 else l1 end as order_id,
item
FROM
CTE
order by
order_id asc;

[@arjundev4908]

WITH CTE AS(SELECT *,
CASE WHEN order_ID%2=1 THEN order_id+1 ELSE order_id-1 END AS REV,
count(*)OVER() AS CNT
FROM ORDERS)
SELECT
CASE WHEN REV > CNT THEN CNT ELSE REV END AS NEW_order_id,ITEM
FROM CTE
ORDER BY NEW_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;

[@BabaiChakraborty-ss8pt]

here is my solution
WITH rn as (
SELECT *,
LEAD(item, 1) OVER () AS item_lead,
LAG(item, 1) OVER () AS item_lag,
ROW_NUMBER() OVER(ORDER BY order_id) AS id_row
FROM public.orders
)
SELECT order_id,
CASE
WHEN id_row % 2= 1 THEN COALESCE(item_lead, item)
WHEN id_row % 2= 0 THEN COALESCE(item_lag, item)
WHEN item_lead IS NULl THEN item
ELSE item END AS new_item
FROM rn;

[@bavi5409]

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