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 
    

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: