Get Top 3 Scores for Each Month For Each Person in a Pool

Question

I have a PostgreSQL DB that tracks the scores of people in a Magic pool. Each player in the pool can play multiple games in a given month and sometimes multiple ones on a given day.

For example player 1, in October played 5 games for a total of 11 points and played 6 games in November for a total of 62 points based on all games played for each month

Game#   Game Date     Points Earned
1      02-Oct-2020      3
2      02-Oct-2020      2
3      09-Oct-2020      3
4      10-Oct-2020      1
5      24-Oct-2020      2
6      02-Nov-2020      4
7      06-Nov-2020      32
8      06-Nov-2020     -4
9      07-Nov-2020      33
10     07-Nov-2020      1
11     10-Nov-2020     -4

What I'm trying to do is get the top 3 scores for each month for the player. Which would total for October as 8 points (3+2+3) and in November 69 points (32+33+4).

When I had only one month, I have no issue getting the top 3 scores for the month; but now that I have multiple months, I can't get my code to work. I have modified my original code and this is what I've come up with

SELECT
  player_name, 
  EXTRACT(MONTH FROM game_date) AS game_month,
  sum(player_pts) AS monthly_pts
FROM testdb.player_points
WHERE player_name = 'player1'
    GROUP BY player_name, game_month
    ORDER BY game_month
    LIMIT 3;

What the above code is returning me, is the point totals for each month. 11 for October and 62 for November, which based on my code is what I expect; but not what I want. I have tried to use INNER JOINS, partitions based on the month, sub-queries; but nothing seems to give me what I'm looking for. The cleanest results I've had is the above code. I'm looking for a little guidance as to how to get what I need. Thanks in advance.

Answer 1

If you want the top three per player and per month, use window functions:

select *
from (
    select pp.*, 
        row_number() over(partition player_name, date_trunc('month', game_date) order by player_pts desc) rn
    from testdb.player_points pp
) pp
where rn <= 3

Then you can aggregate the results by player and month, if that's what you want:

select player_name, game_month, sum(player_points) top_3_points
from (
    select pp.*, d.*
        row_number() over(partition pp.player_name, d.game_month order by pp.player_pts desc) rn
    from testdb.player_points pp
    cross join lateral (values (date_trunc('month', game_date))) d(game_month)
) pp
where rn <= 3
group by player_name, game_month

I moved the date truncation to a lateral join so I don't have to type it twice. If you want the results for a particular player, then you can use a where clause (it would be better placed directly in the subquery).