Perform Weighted Ranking with Percent Rank

[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.