select min(abs(p2.x-p1.x)) as shortest from Point p1 inner join Point p2 where p1.x
@meenayegan66342 жыл бұрын
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
@abhishekasane263510 ай бұрын
Today Interviewer asked me same question
@EverydayDataScience10 ай бұрын
Amazing, hopefully you were able to answer it and will get past to next round. All the best 😊
@prakhargupta20665 ай бұрын
Which company
@anuragkumawat70698 ай бұрын
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-Joshna9 ай бұрын
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
@prnvsgr2 жыл бұрын
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.
@dibakarmandal21482 жыл бұрын
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
@pushankarmakar17839 ай бұрын
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
@mlvprasadofficial2 жыл бұрын
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