Calculate Median
You're a backend engineer working on an online education platform similar to Khan Academy, called "Con Academy".
You're still writing the software that grades on a curve that we started in Curving Grades 1.
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 |
You are building a dashboard for the teachers, and the dashboard will display the median grade score for the classes. The median is the "value in the middle" when all grade score values are sorted sequentially. The "middle" is unambiguous when the number of scores is an odd number. If the number of scores is an even number, and hence there is no clear "middle" value, then the arithmetic mean of the 2 middle values is used.
Select the ID for each class and the median grade score.
Sort them in descending order by median.
Do this without using the percentile_cont() function!
This is an example of a set-aggregate function which we'll cover later.
Instead, use your knowledge of window functions to calculate it manually.
No, there is no median() function or anything like that.
