Compare Related Rows Using Lag and Lead
If you have a list of competitors and their finishing times, how do you determine how much time separated each competitor? Lag and lead allow you to compare rows in order -- for instance, comparing the 1st and 2nd place finishers and the 2nd and 3rd place finishers.
[00:00] Lag and lead allow us to compare the values of rows to the rows next to us. If we have a list of runners in a race, and they're in different divisions, then we can compare to the runners who are next to them in the position.
[00:21] In division one, place one, there's no one who they're lagging behind. There is no row that's ahead of them. In place two, we can ask what the pace is of the runner who is just one row faster than us. Lead is going to be pretty much the same thing but in reverse. They led the second-place runner. The second-place runner's pace was 363.
[00:55] This will kind of crisscross in this way. We can start to compare the different values of the rows next to us. This is really the leader's pace and the lagger's pace. This is the pace of the person in front of us. This is the pace of the person behind us.
[01:17] Now, this allows us to ask questions like how much did we win by? How much did we lose by? We can make this a subquery. We'll call this division places and bump it in. We can select from it.
[01:37] Then we can perform some manipulation so we can say the lagger's pace minus our pace is how much we won by. Our pace minus the leader's pace is how much we lost by. We can see the first place person did not lose by anything. They won.
[01:56] Again, the second-place person how much they lost by, how much they won by. This will kind of keep crisscrossing here. This allows us to ask questions about each runner and maybe mail them some information telling them how much work they would need to do to bump up a place.