00:00What's going on everybody? Welcome back to another video. Today we are continuing our PostgreSQL series
00:04and in this lesson we are taking a look at more window functions with lag, lead, and end tile.
00:10Now in the last lesson we looked at some window functions like row number, rank, dense rank, rolling averages, rolling
00:17sums
00:17and in this lesson we're taking a look at some different ones. Now lag and lead I think are fairly
00:22straightforward.
00:23I think you're going to pick those up right away. Then we have a unique one called end tile
00:27which is really good for splitting your rows into buckets or percentiles and it's super, super useful.
00:33So let's see how we can do this. Let's copy this really quick because we will use that.
00:39The first one that we're going to look at is lag. Now all lag means is we're looking at the
00:44value
00:44before it and lead means we're looking at the value that's leading it or after it and so let's take
00:50a
00:50look at how we can use this. So we're going to take the character name and we'll take the estimated
00:56net worth. We'll do our comma and we're going to use our lag and of course with a window function
01:03we have to say over. Now I'm going to say as lags just so we have that as a column
01:08place header.
01:09Now similarly to an aggregate function we have to pass through a value in our lag that we're actually
01:14going to be looking at. So we're going to be using this estimated net worth. Now we're not doing
01:20anything in the over with partitioning or ordering by just yet. We're just kind of keeping it at its
01:25simplest terms. So with the lag we're taking the estimated net worth from the lagging value which
01:32is behind it. Now with Luke Skywalker he doesn't have somebody or a row above it so it's just going
01:37to be null here. But with Leia Organa we have five million here. We're taking the lagged value which is
01:43right here and placing it on the same row as Leia Organa. So then we have Han Solo. We're taking
01:50this
01:50value placing it here. Chewbacca we're taking Han Solo's and placing it here. We're looking at the
01:56lagged value. Now this is in a generic output right. We haven't specified any ordering so let's go and
02:02actually do that. So let's say we want to order by the estimated net worth from highest to lowest.
02:09So we can look at the previous person's value. So here we have Darth Vader. He's at the tippity top
02:15so he has nothing before it. But then we have Padme and we can see she has eight million. I
02:20think
02:20Darth Vader's is ten million. And so we're comparing this value to the value before it. And it goes down
02:26the line where we take the eight million put it here. Take the five million put it here. So we
02:30can
02:31compare those values to the lagged value. Like any other window function though we can also partition
02:37this. So let's come right here and let's say we want to part and let me spell that right partition
02:44by and we'll do the species. So let's add our species in here. And now we're going to go species
02:51by species and look at the lagged value. So of course within our droids we only have two values so
02:57the first one's not going to have one. But then C-3PO will compare it to our two D2s. So
03:02here's his
03:03value with the lagged value. And then with Gungan there's only one. So within that species there isn't
03:09a lagged value so we won't have one. And then within the humans which is all right here we're
03:14comparing it from highest to lowest just within that partition of species. So we have nothing for
03:21Darth Vader but then we're taking Darth Vader's and comparing it to Padme and then Leia and then Han
03:28and then Obi-Wan always looking at that lagged value. Now we can do the exact same thing with lead.
03:34It's basically the opposite. That's all it is. Now we're taking a look at the value after it.
03:39So let's go ahead and run this. So now with R2-D2 we have his value. We're looking ahead at
03:45the lead
03:46value and pulling it back. So if we come down here to the humans we now can compare Darth Vader's
03:52value
03:52to Padme's. But now we're taking the value ahead and pulling it back instead of taking the value back
03:58and pulling it ahead like we did with lag. The only difference now is that Luke Skywalker at the very
04:03bottom doesn't have a value to compare it against because there is no value that leads his value.
04:08So that is lag and lead in a nutshell. They are very very similar. One just looks at the previous
04:14value.
04:14One looks at the value behind it. Now let's copy this and let's come down here. Let's look at
04:21end tile. Now end tiles are great because they create little buckets or percentiles and that's super useful.
04:27And so what we're going to do is we are going to get the character name. Let's actually just grab
04:33all of this and place this right down here. But now we're going to do end tile. Now end tile
04:42still
04:42needs that over and we can say this as end tiles. We still need that over but we're not passing
04:48through
04:48a column here. We're passing through how many buckets we want. So let's say we wanted four buckets
04:54of 25%. We put a four here or let's say we want two buckets. That would be 50% and
05:0050%. Now let's just
05:01run this as is with nothing else other than just character name, species and estimated net worth. Now
05:08this is kind of just a generic output, right? There's no specifying what we're doing or how we're ordering
05:13it. This is just as the data sits in our table. But you can see here with our two percentiles,
05:19this is going
05:19to be our top 50% and then this is going to be our bottom 50%. With end tiles though,
05:25you really kind
05:26of have to use this over and let's just do it by we'll do it the estimated net worth descending.
05:34So
05:35all this is going to do, let's run this, is we're saying take the estimated net worth from highest to
05:41lowest and then break it up into end tiles. And sorry, I had a glitch on there. It just like
05:45popped up
05:46and would not go away. But we have our Darth Vader here. This is our top 50% based off
05:51of the estimated
05:52net worth. And then this is the bottom 50% based off of the estimated net worth. Now just like
05:58we did
05:59with every single other one, we can also partition. So we can take a look at the top 50%
06:04based off of
06:06something like the species. So now let's run this. And within each species, we're going to be able to
06:13look at the top 50%. So R2D2, he's our top 50%. C3PO, he's our bottom 50%. Within human, within this
06:21partition, we have our top 50% and our bottom 50%. Now you can have as many or as little
06:28buckets as
06:28you want. You can have five, you could have 20, but that would only make sense if you need 20
06:34buckets.
06:35But we can now break this apart. Now there's so many use cases for this. Maybe you want to break
06:40it up
06:40into age brackets. Maybe you want to break it up by how much spending somebody is doing. It just depends
06:45on what you're trying to get out of it. But end tiles can be really, really useful. So that's all
06:49we're going to be taking a look at in this lesson. That's lag, lead and end tile. I hope you
06:53learned
06:53something in this video. If you did, be sure to like and subscribe and I will see you in the
06:57next lesson.
