Q:

Grouping by to find average differences for specific indexes in SQL

I have the following table:
person_index   score   year
3              76      2003
3              86      2004
3              86      2005
3              87      2006
4              55      2005
4              91      2006

I want to group by person_index, getting the average score difference between consecutive years, such that I end up with one row per person, indicating the average increase/decrease:
person_index   avg(score_diff)   
3              3.67      
4              36

So for person with index 3 - there were changes over 3 years, one was 10pt, one was 0, and one was 1pt. Therefore, their average score_diff is 3.67.
EDIT: to clarify, scores can also decrease. And years aren't necessarily consecutive (one person might not get a score at a certain year, so could be 2013 followed by 2015).

A:

Simplest way is to use LAG(MySQL 8.0+):
WITH cte AS (
  SELECT *, score - LAG(score) OVER(PARTITION BY person_index ORDER BY year) AS diff
  FROM tab
)
SELECT person_index, AVG(diff) AS avg_diff
FROM cte
GROUP BY person_index;

db<>fiddle demo
Output:
+---------------+----------+
| person_index  | avg_diff |
+---------------+----------+
|            3  |   3.6667 |
|            4  |  36.0000 |
+---------------+----------+

