Existe uma tabela do Postgres em nosso sistema chamada "pontuações" que registra as pontuações dos testes. Ele contém os examinandos (usuários do nosso sistema), pontuações de 0 a 10, departamentos e datas de cada teste feito, assim:
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
...
Um usuário pode ter mais de uma pontuação por departamento.
O que eu quero é: Se uma pessoa fez mais de 4 testes em um depto, a média das últimas 5 notas por usuário por depto.
Sinto que estou quase lá com esta consulta:
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;
No entanto, os depts com menos de 5 pontuações aparecem :(. Deve haver algo errado na consulta a ver com as funções da janela, mas não consigo identificar ...
Existe uma maneira melhor de escrever esta consulta?