Join egghead, unlock knowledge.

Want more egghead?

This lesson is for members. Join us? Get access to all 3,000+ tutorials + a community with expert developers around the world.

Unlock This Lesson

Already subscribed? Sign In

Autoplay

    Perform Weighted Ranking with Percent Rank

    Brett CassetteBrett Cassette

    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.

    postgresPostgreSQL
    Code

    Code

    Become a Member to view code

    You must be a Member to view code

    Access all courses and lessons, track your progress, gain confidence and expertise.

    Become a Member
    and unlock code for this lesson
    Transcript

    Transcript

    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.

    Discuss

    Discuss