Video summary
This video continues a series on PostgreSQL window functions by introducing three specific tools: LAG, LEAD, and NTILE. The instructor explains that LAG and LEAD are designed to access values from adjacent rows without requiring aggregation, making them ideal for comparing current data points with their neighbors. LAG retrieves the value from the preceding row, while LEAD fetches the value from the following row. A key feature of these functions is their ability to return NULL when there is no preceding or succeeding row within the defined scope, which naturally occurs at the top and bottom of an ordered dataset.
To demonstrate practical usage, the tutorial walks through examples using a character database sorted by estimated net worth. When ordering from highest to lowest, LAG allows users to see how much wealth a character has compared to the person immediately above them in the list, whereas LEAD shows the comparison with the person below. The video also highlights how these functions can be combined with PARTITION BY clauses to perform comparisons within specific groups, such as different species. This ensures that calculations are isolated per group; for instance, comparing net worth only among humans or droids, rather than across the entire mixed dataset.
The lesson concludes by introducing NTILE, a function specifically useful for splitting rows into equal-sized buckets or percentiles. Unlike LAG and LEAD, NTILE does not require an input column but instead takes an integer argument specifying the number of buckets desired, such as dividing data into four quartiles or two halves. The instructor demonstrates how to order data by net worth in descending order and then apply NTILE to categorize characters into top and bottom segments based on wealth. Like the other window functions, NTILE can also be partitioned by categories like species, allowing analysts to identify high-performing individuals within specific subgroups, such as determining which droid or human falls into the top percentile of their respective groups.
Read the full video transcript
What's going on everybody? Welcome back
to another video. Today we are
continuing our Postgra SQL series. And
in this lesson we are taking a look at
more window functions with lag, lead and
end tile. Now in the last lesson we
looked at some window functions like row
number, rank, dense rank, rolling
averages, rolling sums. And in this
lesson we're taking a look at some
different ones. Now lag and lead I think
are fairly straightforward. I think
you're going to pick those up right
away. Then we have a unique one called
end tile which is really good for
splitting your rows into buckets or
percentiles and it's super super useful.
So let's see how we can do this. Let's
copy this really quick because we will
use that. The first one that we're going
to look at is lag. Now all lag means is
we're looking at the value before it.
And lead means we're looking at the
value that's leading it or after it. And
so let's take a look at how we can use
this. So we're going to take the
character name and we'll take the
estimated net worth. We'll do our comma
and we're going to use our lag. And of
course with a window function we have to
say over. Now I'm going to say as lags
just so we have that as a column place
header. Now similarly to an aggregate
function we have to pass through a value
in our lag that we're actually going to
be looking at. So we're going to be
using this estimated net worth. Now,
we're not doing anything in the over
with partitioning or ordering by just
yet. We're just kind of keeping it at
its simplest terms. So, with the lag,
we're taking the estimated net worth
from the lagging value, which is behind
it. Now, with Luke Skywalker, he doesn't
have somebody or a row above it. So,
it's just going to be null here. But
with Leo Orana, we have 5 million here.
We're taking the lagged value, which is
right here, and placing it on the same
row as Leia Orana. So then we have Han
Solo. We're taking this value, placing
it here. Chewbacca, we're taking Han
Solo's and placing it here. We're
looking at the lagged value. Now this is
in a generic output, right? We haven't
specified any ordering. So let's go and
actually do that. So let's say we want
to order by the estimated net worth from
highest to lowest. So we can look at the
previous person's value. So here we have
Darth Vader. He's at the tippity top. So
he has nothing before it. But then we
have Padme and we can see she has 8
million. I think Darth Vader's is 10
million. And so we're comparing this
value to the value before it. And it
goes down the line where we take the 8
million, put it here, take the 5
million, put it here. So we can compare
those values to the lagged value. Like
any other window function though, we can
also partition this. So let's come right
here and let's say we want to part and
let me spell that right. partition
by and we'll do the species. So, let's
add our species in here. And now we're
going to go species by species and look
at the lag to value. So, of course,
within our droids, we only have two
values. So, the first one's not going to
have one, but then C3PO will compare it
to R2-D2s. So, here's his value with the
lagged value. And then with Gungan,
there's only one. So, within that
species, there isn't a lagged value, so
we won't have one. And then within the
humans, which is all right here, we're
comparing it from highest to lowest just
within that partition of species. So we
have nothing for Darth Vader. But then
we're taking Darth Vader's and comparing
it to Padme and then Leia and then Han
and then Obi-Wan. Always looking at that
lagged value. Now we can do the exact
same thing with lead. It's basically the
opposite. That's all it is. Now we're
taking a look at the value after it. So
let's go ahead and run this. So now with
R2-D2, we have his value. We're looking
ahead at the lead value and pulling it
back. So if we come down here to the
humans, we now can compare Darth Vader's
value to Padme, but now we're taking the
value ahead and pulling it back instead
of taking the value back and pulling it
ahead like we did with lag. The only
difference now is that Luke Skywalker at
the very bottom doesn't have a value to
compare it against because there is no
value that leads his value. So that is
lag and lead in a nutshell. They are
very very similar. One just looks at the
previous value, one looks at the value
behind it. Now let's copy this and let's
come down here and let's look at end
tile. Now end tiles are great because
they create little buckets or
percentiles and that's super useful. And
so what we're going to do is we are
going to get the character name. Let's
actually just grab all of this and place
this right down here. But now, oops,
we're going to do end tile.
Now, end tile still needs that over. And
we can say this as end tiles. We still
need that over, but we're not passing
through a column here. We're passing
through how many buckets we want. So,
let's say we wanted four buckets of 25%,
we'd put a four here. Or let's say we
want two buckets. That would be 50% and
50%. Now, let's just run this as is with
nothing else other than just character
names, species, and estimated net worth.
Now, this is kind of just a generic
output, right? There's no specifying
what we're doing or how we're ordering
it. This is just as the data sits in our
table. But you can see here with our two
percentiles, this is going to be our top
50% and then this is going to be our
bottom 50%. With end dials though, you
really kind of have to use this over.
And let's just do it by we'll do it the
estimated net worth descending. So all
this is going to do let's run this is
we're saying take the estimated net
worth from highest to lowest and then
break it up into end tiles. And sorry I
had a glitch on there. It just like
popped up and would not go away. But we
have our Darth Vader here. This is our
top 50% based off of the estimated net
worth. And then this is the bottom 50%
based off of the estimated net worth.
Now, just like we did with every single
other one, we can also partition. So, we
can take a look at the top 50% based off
of something like the species. So, now
let's run this. And within each species,
we're going to be able to look at the
top 50%. So, R2-D2, he's our top 50%.
C3PO, he's our bottom 50%. within human
within this partition we have our top
50% and our bottom 50%. Now you can have
as many or as little buckets as you
want. You can have five. You could have
20. Uh but that would only make sense if
you need 20 buckets. But we can now
break this apart. Now there's so many
use cases for this. Maybe you want to
break it up into age brackets. Maybe you
want to break it up by how much spending
somebody is doing. It just depends on
what you're trying to get out of it. But
end tiles can be really really useful.
So, that's all we're going to be taking
a look at in this lesson. That's lag,
lead, and endile. I hope you learned
something in this video. If you did, be
sure to like and subscribe, [music] and
I will see you in the next lesson.