LeetCode Interview SQL Question with Detailed Explanation | Practice SQL | LeetCode 613

[@pankajchandel1000]

select min(abs(p2.x-p1.x)) as shortest from Point p1 inner join Point p2 where p1.x

[@meenayegan6634]

Cross join or windows_function? which one has minimum run_time? Just using an windows function here.... select min(distance) from ( select x, lead (x) over (order by x) as next_pos, lead (x) over (order by x) - next_pos as distance from points )x

[@anjanijanyavula373]

Yep done the same

[@abhishekasane2635]

Today Interviewer asked me same question

[@EverydayDataScience]

Amazing, hopefully you were able to answer it and will get past to next round. All the best 😊

[@prakhargupta2066]

Which company

[@anuragkumawat7069]

We can do it by window function . Or we can create a CTE with rank of the rows. Then join the rank with the next rank+ 1.

[@imdeepu7855]

select (ax-bx) as shortest from (select x as ax from distance d1) a join (select x as bx from distance d1) b where (a.ax-b.bx)=1

[@nikhilvyas9497]

It can be solved using Window Function too i guess select MIN(abs( next_pos - x )) as Distance FROM (select x, Lead(x) over( order by x) as next_pos from point order by x) a

[@27-Joshna]

From this we are able to get only next position value ie consecutive distance but in order to get a solution we need all possible combinations so i think it doesn't work

[@prnvsgr]

As you told cross join doesn't require 'on' statement , does it goes same for inner join ..as i tried to use 'on' statement " on p1.x = p2.x" but it is giving null value as shortest value.

[@dibakarmandal2148]

It is database dependent scenerio, if you use inner join without on clause it will not work in SQL SERVER but in MYSQL that behave as cross join.

[@nikitasinha8181]

Thank you so much sir

[@raulkishore]

Can you explain how it works for duplicate records

[@pushankarmakar1783]

exactly

[@yashwani209]

Can this also be done through lead and lag? If table is arranged in ascending order

[@anjanijanyavula373]

No they have't asked for consecutive distance . We have check through all possibility distance so cross is best method.

[@yashwani209]

@@anjanijanyavula373 nope first sort the table and then use lead lag

[@mlvprasadofficial]

12 th

[@AmanAshutoshBCS]

BUT sir, hum kuch aisa nhi kar sakte hai ki .... Kyunki data ascending order main hai. hence humein to uske next row ke sath compare karna chasiye

[@AmanAshutoshBCS]

i think by using some windos function we can do that