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
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