This Lesson is for Members

Subscribe today and get access to all lessons! Plus direct HD download for offline use, enhances transcripts, member comment forums, and iTunes "podcast" RSS feed. Level up your skills now!

Unlock This Lesson

Already subscribed? Sign In

Autoplay

    Compare Related Rows Using Lag and Lead

    Brett CassetteBrett Cassette

    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.

    Code

    Code

    Become a Member to view code

    You must be a Member to view code

    Access all courses and lessons, track your progress, gain confidence and expertise.

    Become a Member
    and unlock code for this lesson
    Transcript

    Transcript

    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.

    Discuss

    Discuss