Compute the deviance of each person's ACT score from the average ACT score from all students in the table below called
student_ACT_scores. Return first_name, score and ACT_score_deviation_mean with deviance in descending order.
We can use a window function to apply an aggregate function to all rows in a table. The code
AVG(ACT_score) OVER () applies the average ACT score over the entire table. There is no re-ordering of rows or partioning of groups in the window function.
Given that new average ACT score in a column, we can perform the subtraction operation in the
SELECT statement. Lastly, we order records from highest
CT_score_deviation_mean to smallest.
SELECT *, ACT_score - AVG(ACT_score) OVER () AS ACT_score_deviation_mean FROM student_ACT_scores ORDER BY ACT_score_deviation_mean DESC