PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – In Postgres, latest 5 scores per user returning username

Based on this question and answer how is the query modified to return the user’s name from a foreign table rather than the id? Here’s the sql from the answer of the linked thread:

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;
Tags:

2

Answers


  1. 0

    You can join then user table to the subselect

    SELECT x.user_id,u.user_name, 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 
JOIN user u ON x.user_id = u.user_id
WHERE  x.rn <= 5
GROUP  BY user_id;
    Login or Signup to reply.
  2. 0

    First aggregate, then join:

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

    But the best solution depends on undisclosed details. If referential integrity is not guaranteed, you might use a LEFT JOIN. If you only want results for a few given users a different query is much more efficient …

    Especially if some users might not have any scores (yet), this form is the safe course of action (preserving all users):

    SELECT u.user_id, u.name, s.*
FROM   users u
LEFT   JOIN LATERAL (
   SELECT round(avg(score)::numeric, 2) AS sc_avg
   FROM  (
      SELECT s.score
      FROM   mg.scores s
      WHERE  s.user_id = u.user_id
      AND    s.score IS NOT NULL
      ORDER  BY s.date DESC NULLS LAST
      LIMIT  5
      ) AS sub
   ) s ON true;

    See:

    Either way, have an index on mg.scores (user_id, date DESC NULLS LAST) or similar. And on users (user_id). Details depend on the full case.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search