SQL Window Functions Problem Solution

Score Deviation from Mean

Problem Statement

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.

Name ACT_score
Jake 32
Maddie 25
Luis 28
Angela 30

Solution

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.

SQL Query:

SELECT *, 
       ACT_score - AVG(ACT_score) OVER () AS ACT_score_deviation_mean
       FROM student_ACT_scores
       ORDER BY ACT_score_deviation_mean DESC

Returned Output:

Name ACT_score ACT_score_deviation_mean
Jake 32 3
Angela 30 1
Luis 28 -1
Maddie 26 -3