How can I get the latest five scores per user per subject?

Question

There is a Postgres table in our system called "scores" which records test scores. It has test takers (users of our system), scores from 0-10, depts and dates each time a test was taken, like this:

id  |  user_id  |  score  |  dept      | cdatetime
---------------------------------------------------------
1   |  123      |  5.5    | math       | 2014-01-01 12:00 
2   |  123      |  8.9    | chemistry  | 2014-02-01 03:20 
3   |  123      |  0.2    | physics    | 2014-01-01 09:00 
4   |  123      |  1.4    | math       | 2014-03-01 12:00 
5   |  456      |  9      | biology    | 2014-01-18 05:20 
...

A user can have more than one score per dept.

What I want is: If a person took more than 4 tests in a dept, the average of the last 5 scores per user per dept.

I feel I'm almost there with this query:

SELECT user_id, round(avg(score)::numeric, 2) AS sc_avg, dept
FROM  (
   SELECT *
         ,row_number() OVER (PARTITION BY user_id, dept ORDER BY cdatetime DESC) AS rn
  FROM mg.scores
  WHERE score IS NOT NULL) as score
   ) AS x
WHERE  x.rn <= 5
GROUP  BY user_id,dept;

However, the depts with less than 5 scores show up :(. There must be something that is wrong in the query to do with window functions, I can't spot it though...

Is there a better way to write this query?

Answer 1

Do you want the ones with less than 5 scores in mix. If you want only the 5 scores one, you can add a having to your query:

    SELECT user_id, round(avg(score)::numeric, 2) AS sc_avg, dept
    FROM  (
       SELECT *
             ,row_number() OVER (PARTITION BY user_id, dept ORDER BY cdatetime DESC) AS rn
      FROM mg.scores
      WHERE score IS NOT NULL) as score
       ) AS x
    WHERE  x.rn <= 5
    GROUP  BY user_id,dept
    HAVING count(*) > 4;