Name: Perform Weighted Ranking with Percent Rank
Uploaded: 2017-01-13T15:55:36-06:00
Description: 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.

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

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. 

https://app.egghead.io/api/v1/lessons/postgresql-perform-weighted-ranking-with-percent-rank/subtitles

[00:00] We've got a list of grade data from different schools. We'll take a look at this. We can see that the school here is abbreviated. GP and MS are our two different schools. The G3 is their final grade for the year. This is the number of points possible. This isn't a percentage and a percentage is probably what we want to send home.

[00:24] How are we going to find out the percentage? First, we'll take a look at the max G3 for each school. This will be different for each school, because there were a max number of points that were different in each school. Take a look at that. We can see for GP, all of these records have a max score of 20. Then for MS, we see 19. This is the max final grade.

[00:50] Then we want to obtain some kind of a percentage, so we'll use percent rank and we'll partition by the school. Again, if we forget to use some kind of ordering clause, now everyone's percent rank is zero, because we haven't defined how to order them.

[01:05] We'll say, "Order by the G3, ascending." This will give us a percentage here. This doesn't quite look right. We'd like it to be maybe a rounder percentage, something nice to send home. We can use a round, multiply by 100, and say, "This is our weighted final grade."

[01:35] Now we can see everyone who has scored 0out of 20 points falls in the 0th percent. They get zero percent. Moving up, we would expect that the best student in each school is going to get 100 percent when they have the most points possible. Then things will buck it off from there.

Perform Weighted Ranking with Percent Rank

Share this video with your friends

Course

Using Postgres Window Functions