How to rank on aggregated sum in Postgresql?

Question

I want to rank by aggregated points. Example: A guessing game. Day 1: Person A guesses and gets 10 points, person B guesses and gets 9 points. Day 2: Person A gets 5 points, Person B gets 9.

What I want to get is: On Day 2, Person A has an aggregated amount of 15 points and ranks 2.

Here's the basic table guesses:

id, person, points, day
1, thomas, 10, 1
2,thomas,5,2
3,marie,9,1
4,marie,9,2

I'm having no problems getting the aggregated points grouped by day:

SELECT
    *,
    sum(points) OVER (PARTITION BY person ORDER BY id) AS total_running_points,
FROM
    guesses
ORDER BY
    day asc;

But now I need to rank on every day.

I tried with the following but failed as of course total_running_points is a new alias:

SELECT
    *,
    sum(points) OVER (PARTITION BY person ORDER BY id) AS total_running_points,
    rank() OVER (ORDER BY total_running_points desc)
FROM
    bets_by_day
ORDER BY
    day asc;

I sense that I should use a subquery but then I wonder how to partition on it.

How can I solve this?

Answer 1

You can use a subquery:

SELECT b.*, rank() over (order by total_running_points desc) rnk
FROM (
    SELECT b.*, sum(points) over (partition by person order by id) AS total_running_points
    FROM bets_by_day b
) b
ORDER BY day asc;