Name: Define Windows Using Multiple Factors
Uploaded: 2017-01-13T16:42:07-06:00
Description: 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.

Learn What A Window Function Can Do

Rank Records with Rank and Dense Rank

Perform Weighted Ranking with Percent Rank

Define Windows Using Multiple Factors

Find Percentiles Using ntile

Create Running Totals Using Window Functions

Understand Frame Clauses

Compare Related Rows Using Lag and Lead

Compare the Outliers Using First Value and Last Value

Combine Window Functions to Answer Complex Questions

Using Postgres Window Functions

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.

https://app.egghead.io/api/v1/lessons/postgresql-define-windows-using-multiple-factors/subtitles

[00:00] Here, we have a set of student grade data. We have information on how these students perform in relationship to each other. So far, we've only partitioned by the school. That creates a bucket for each school. We also have information about whether or not the student had access to the Internet.

[00:17] We might hypothesize that students who have access to the Internet perform better as a group than students who don't. To test this hypothesis, we can create both buckets using multiple factors. We can have one for GP with access to the Internet, one with GP without access to the Internet, and the same thing for MS.

[00:36] We'll go ahead and create our buckets using multiple factors. Now we have multiple columns here for each student, one which considers what the max final grade would be for all students in the school, one which considers only in their peer group of students who do or do not have access to the Internet. This student does not have access.

[01:02] In their peer group, we can see that students without access perform two points worse as a whole. Then we have their weighted final grade, if we consider them in the context of all students versus the weighted final grade only in their peer group. We can see students who don't have access to the Internet do perform worse as a group.

[01:21] If we consider them only in the context of students who don't have access to the Internet, they tend to perform better than they would considered in the context of the entire school. The same thing seems to be true for students who do have access to the Internet as a whole.

[01:38] They perform the same, or a little bit worse, when we consider them only with privileged students who have this kind of Internet access. You can play around with the data a little bit more yourself, and see that the same thing holds up and is true for the students at MS.

Define Windows Using Multiple Factors

Share this video with your friends

Course

Using Postgres Window Functions