Master the SQL SELECT statement part 08: Window Functions

This video is part of a multi-part series on mastering the SQL SELECT statement. The SELECT statement, the most often used SQL command, is easy to learn yet difficult to master. Follow along with me and level up your database query super powers!

This video covers Window functions which appear in the projection and operate over the common-table expression, producing a single value in each row. Window functions make it easier for us to write complex SQL queries without the need to write sub-queries or use the WITH clause.

Window functions return a value in the row/column intersection based on the selected partition of data in the common table expression of the query.
The video demonstrates the three types of window functions:

1. Aggregate window functions like SUM(), MIN(), MAX(), AVG(), and COUNT() that return a summary value based on the chosen partition.
2. Value window functions like LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE() that retrieve a positional value based on the chosen partition and sort order
3. Ranking window functions like ROW_NUMBER(), RANK() and NTILE() that return an ordinal position as it value based on the chosen partition and sort order.

[StefanoVerugi]

I agree, totally underrated channel, no frills straight to the point, practical solutions
thank you

[CozmoNz]

Well made videos man, you deserve alot more views!

[sinem]

The best explanation of window functions I've come across so far. Thank you so much got making this complicated concept so accessible. Please keep up the good work!
Also kudos for coming up with such hilarious student names! Buck Naked is my favorite!

[rockymarquiss]

If you add into the window - range between unbounded preceding and unbounded following your first example of the best gpa would've worked.

select student_firstname, student_lastname, major_name,
last_value(student_firstname) over(partition by major_name order by student_gpa range between unbounded preceding and unbounded following) as best_student_first_Name,
last_value(student_lastname) over(partition by major_name order by student_gpa range between unbounded preceding and unbounded following) as best_student_last_Name
from students
join majors on student_major_id = major_id