Using Postgres Window Functions

19 minutes

In this course we will show you how to wield the power behind the elusive window function. Which five movies were the highest grossing each year? Who are the highest performers in each department relative to their peers? These questions ask us to partition records into subgroups and answer questions about those groups -- that’s the power of a window function.

Many programmers will reach for their favorite scripting language to solve problems like these, but window functions allow you to answer them with the speed and efficiency of the Postgres query planner.

Plus, with the power to further slice and dice this derivative information, there’s no limit to the questions you can answer with raw SQL.

If you’re interested in the Postgres client that is used in this course you can download it here to check it out and follow along.

pro-course-rss-logo

PRO RSS Feed

Learn What A Window Function Can Do

Rank Records with Rank and Dense Rank

Perform Weighted Ranking with Percent Rank

P

Define Windows Using Multiple Factors

P

Find Percentiles Using ntile

P

Create Running Totals Using Window Functions

P

Understand Frame Clauses

P

Compare Related Rows Using Lag and Lead

P

Compare the Outliers Using First Value and Last Value

Combine Window Functions to Answer Complex Questions

P
postgres tutorial about Learn What A Window Function Can Do

Learn What A Window Function Can Do

1:47 postgres

In this lesson we’ll see how we can partition movies into the years they were released, and find the top 5 rated titles each year. The power of the window function is to partition data into groups and answer questions about those groups.

postgres tutorial about Rank Records with Rank and Dense Rank

Rank Records with Rank and Dense Rank

1:35 postgres

If you have a list of competitors in a race, and a list of their finishing times, how do you create a list of what place they came in? Rank and dense rank can help you out; they differ in how they handle ties.

postgres tutorial about Perform Weighted Ranking with Percent Rank

Perform Weighted Ranking with Percent Rank

1:55 postgres PRO

What if you had a list of student grades and wanted to give them a weighted grade for the school year? Percent rank is used to answer frequency distribution questions.

postgres tutorial about Define Windows Using Multiple Factors

Define Windows Using Multiple Factors

1:55 postgres PRO

Weighted ranking on a single factor can hide lots of information. For instance, students without access to internet perform 1-3 full letter grades worse than their peers with internet access. A teacher that wanted to consider student privilege could take many factors into account when assigning student grades to get a better sense for how strong individual students were.

postgres tutorial about Find Percentiles Using ntile

Find Percentiles Using ntile

1:44 postgres PRO

What scores do the 90th percentile of students receive? How long does the 99th percentile web request take? Percentiles and arbitrary ntiles provider a richer understanding of underlying data and outliers.

postgres tutorial about Create Running Totals Using Window Functions

Create Running Totals Using Window Functions

1:06 postgres PRO

How could your Kickstarter-type site list the running total for each contribution next to it? Using window functions of course! The default behavior for aggregations under window functions is to create running totals.

postgres tutorial about Understand Frame Clauses

Understand Frame Clauses

2:00 postgres PRO

What if you want aggregations that aren’t running totals? What if you want the sum total for the whole frame? Or the minimum value across the frame? Unbounded preceding, unbounded following, and current row help you define your frame of reference.

postgres tutorial about Compare Related Rows Using Lag and Lead

Compare Related Rows Using Lag and Lead

2:16 postgres PRO

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.

postgres tutorial about Compare the Outliers Using First Value and Last Value

Compare the Outliers Using First Value and Last Value

1:55 postgres

How does each runner compare with the first and last place finisher? What about the nth place finisher? Firstvalue, lastvalue, and nth_value can make these decisions.

postgres tutorial about Combine Window Functions to Answer Complex Questions

Combine Window Functions to Answer Complex Questions

3:38 postgres PRO

Individual window functions are useful, but sometimes you’ll need to combine them to answer complex questions. For instance, which 10 star movies were released in the year that had the most 10 star movies released?

Presented by:

Brett Cassette

Brett is a comedian and writer who has heard of, but never truly experienced, programming

HEY, QUICK QUESTION!
Joel's Head
Why are we asking?