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

Posted by programmingpraxis
Filed in Exercises
1 Comment »

One Response to “Test Scores”

  1. Rainer said
    May 25, 2016 at 1:37 PM

    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

Leave a comment