In Postgres, how can I average the latest 5 scores per user?

Question

I am stuck on a query in Postgres and am getting pretty frustrated. I have a table called scores with three columns:

score, user_id and date

And I want to get the average of all users last 5 scores. This query doesn't exactly get me that:

SELECT user_id, 
       ROUND( AVG( score )::numeric, 2) as sc_avg
FROM 
(SELECT ROW_NUMBER() OVER (PARTITION BY user_id) AS r,
    sc.*
    from mg.scores sc
WHERE score IS NOT NULL
ORDER BY date DESC) AS x
WHERE x.r >= 5
GROUP BY user_id;

Is there a better way to limit the last 5 jobs per user?

You need to add the `ORDER BY date DESC` to the `over()` clause, otherwise the row_number() doesn't return what you think (btw: `date` is a *horrible* name for a column) — , Nov 11 '13 at 09:30

Erwin Brandstetter · Accepted Answer · 2017-09-19T02:26:17.203

As a_horse commented, the ORDER BY clause has to go into the window function.
And since your order is descending, it needs to be <= instead of >=:

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

If date can be NULL, use ORDER BY date DESC NULLS LAST. See:

PostgreSQL sort by datetime asc, null first?

In Postgres, how can I average the latest 5 scores per user?

1 Answers1