Story:
I am trying to get the sum of records and the max date by country and games and another column that would rank the top countries based on the sum of records:
select id, country, game, sum(records) as records, max(date) as max_date
from table
group by id, country, game
It is the country rank column that is giving me trouble. Here's what I tried:
ROW_NUMBER() OVER(PARTITION BY id, country ORDER BY SUM(records) DESC) as rn
All it does is rank the each row partition by country which is what I expected.
Objective
Is there a way to achieve what i want in one or two subqueries?
Here's a desired output
+----+---------+--------------+---------+------------+------+
| id | country | game | records | max_date | rank |
+----+---------+--------------+---------+------------+------+
| 2 | usa | wow | 10 | 2019-01-01 | 1 |
| 2 | usa | wakfu | 15 | 2019-01-01 | 1 |
| 2 | usa | clash royale | 30 | 2019-01-01 | 1 |
| 2 | germany | dofus | 9 | 2019-01-01 | 2 |
+----+---------+--------------+---------+------------+------+
Here for ID #2, country USA is 1st due to its combined sum of records from all games.
To the request of comments below:
Raw data looks like that:
+----+---------+--------------+---------+------------+--+
| id | country | game | records | max_date | |
+----+---------+--------------+---------+------------+--+
| 2 | usa | wow | 2 | 2018-12-01 | |
| 2 | usa | wow | 5 | 2018-12-05 | |
| 2 | usa | wow | 1 | 2018-12-10 | |
| 2 | usa | wow | 2 | 2019-01-01 | |
| 2 | usa | wakfu | 10 | 2018-12-10 | |
| 2 | usa | wakfu | 5 | 2019-01-01 | |
| 2 | usa | clash royale | 30 | 2019-01-01 | |
| 2 | germany | dofus | 2 | 2018-05-01 | |
| 2 | germany | dofus | 4 | 2018-07-01 | |
| 2 | germany | dofus | 3 | 2019-01-01 | |
+----+---------+--------------+---------+------------+--+
You can build on your aggregation query. This version produces a ranking that is like row_number()
, so ties would get different values:
select id, country, game, records, max_date,
dense_rank() over (order by country_sum desc, country) as ranking
from (select id, country, game, sum(records) as records, max(date) as max_date,
sum(sum(records)) over (partition by country) as country_sum
from mytable
group by id, country, game
) cg;
Here is a db<>fiddle.