Curving Grades 1
You're a backend engineer working on an online education platform similar to Khan Academy, called "Con Academy".
Your assignment is to write the software that assigns letter grades based on the students' numerical scores in their class.
The grades are in the grades table, and classes are in the classes table.
| column_name | type | reference |
|---|---|---|
id | VARCHAR | NULL |
subject | VARCHAR | NULL |
name | VARCHAR | NULL |
semester | VARCHAR | NULL |
teacher_id | VARCHAR | teachers.id |
| column_name | type | reference |
|---|---|---|
score | NUMERIC | NULL |
student_id | VARCHAR | students.id |
class_id | VARCHAR | classes.id |
The classes vary in difficulty, and some have much lower average scores. However, you want to make sure the letter grades have the same distribution for each class regardless of difficulty. Therefore, you need to grade on a curve.
Curve grading will be done based on percentiles. The percentile for a student's score is the percentage of students that have a score less than or equal to that score. Therefore, if the highest scoring student gets a 98.90, then the percentile for 98.90 is by definition 100%. Letter grades will be assigned according to the following percentile ranges:
| Grade | Range |
|---|---|
| A | 100 - 80% |
| B | 80 - 50% |
| C | 50 - 25% |
| D | 25 - 10% |
| F | 10 - 0% |
The bottom of each range is non-inclusive. That means 80% gets a B, not an A.
For now, you're just testing this on the 2021 2nd semester session of "Cell Biology", a particularly difficult class on Con Academy.
Limit the letter grade assignments to only the students in that class.
Select each student's numerical score, student_id, class_id, and letter grade (alias it letter_grade).
Order the result with the highest score first.
You might want to look into the
cume_dist()function.
