Finding Cumulative progress with SQL

I have a SQL table as below :

I need to calculate the cumulative progress.

for example S1000 have three f1 score. the progress calculation formula is (95 – 87) + (87 – 80)

for S2000 the calculation would be (75 -17) + (17 -57)

How to achieve this using SQL

Answer

It looks like your progress score is the last minus the first. If there are always three scores, you can use conditional aggregation like this:

select college_id, student_id,
       sum(case when f1 = 3 then score
                when f1 = 1 then - score
           end) as progress
from t
group by college_id, student_id;

If there are not always three scores, you can generalize this:

select college_id, student_id,
       sum(case when f1 = cnt then score
                when f1 = 1 then - score
           end) as progress
from (select t.*,
             count(*) over (partition by college_id, student_id) as cnt
      from t
     ) t
group by college_id, student_id;