Test Scores
May 24, 2016
The high school two blocks from me just had their annual picnic, my youngest daughter just graduated from college, and my primarily academic readership suddenly dropped in half (history suggest it will stay low until mid-August), so it seems to be the right season to have a simple data-processing task involving student test scores.
Given a list of student names and test scores, compute the average of the top five scores for each student. You may assume each student has as least five scores.
Your task is to compute student scores as described above. When you are finished, you are welcome to read or run a suggested solution, or to post your own solution or discuss the exercise in the comments below.
Programming Praxis 
In SQL for DB2:
with students (id,name) as (values (1, 'A'),(2, 'B'),(3, 'C')), scores (id,score,cnt) as (select id, int(rand() * 100), 1 from students union all select id, int(rand() * 100), cnt + 1 from scores where cnt < 10), list (id,cnt,arr) as (select id,cnt,cast(char(score) as varchar(50)) from scores where cnt = 1 union all select s.id,s.cnt,trim(l.arr) concat ',' concat char(s.score) from list l inner join scores s on l.id = s.id and l.cnt + 1 = s.cnt), hits (id,score,rn) as (select id,score,row_number() over (partition by id order by score desc) from scores), average (id,val) as (select id, avg(score) from hits where rn <= 5 group by id) select a.id as student_id, a.val as average_score, l.arr as scores_list from average a inner join list l on a.id = l.id and l.cnt = 10 ==> STUDENT_ID AVERAGE_SCORE SCORES_LIST 1 55 49,49,18,35,15,70,45,7,63,33 2 80 79,59,30,4,79,90,21,61,61,92 3 68 54,42,30,65,59,39,82,18,84,39