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

Pages: 1 2

### One Response to “Test Scores”

1. Rainer said

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