Find closest follower in a rank list

Posted on

Question :

On my database I have the table named Rank_List_T:

Rank Name   Time
1    John   110
2    David  120
3    Bob    121
4    Hans   124
5    Claus  126
6    Gustav 140

I am interested in the person, who has the closest follower.
In the above table David is followed by Bob with a difference of 1.
Hans is followed by Claus with a difference of 2.

I need a SQL-query which returns this:

Name   Time-Difference
David  1
Hans   2
Bob    3
John   10
Claus  14

Yes, this can be done by joining the table RankList_T to it self:

select 
r1.rank,r1.name,r1.time,
r2.rank,r2.name,r2.time,
r2.time-r1.time as time_diff
from GUEST.[RANK_LIST_T] r1
left join GUEST.[RANK_LIST_T] r2 on r1.rank+1=r2.rank
where r2.rank is not null
order by r2.time-r1.time
;

But this has a very poor performane with an algorithm complexity of O(n^2).
I am looking for a more efficient way to do that on a SQL Database.

Please, check the DDL and DML to reproduce the problem.

Answer :

SELECT *, LEAD(time) OVER (ORDER BY time) - time AS delta
FROM RANK_LIST_T
ORDER BY 4;

fiddle

Leave a Reply

Your email address will not be published.