CODEWITHSUNDEEP
sqlsql-servergroup-bygreatest-n-per-group
Roger Steinberg
Roger Steinberg

Reputation: 1604

Get the highest category while performing other type of aggregates

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 |  |
+----+---------+--------------+---------+------------+--+

Upvotes: 2

Views: 96

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

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.

Upvotes: 1

GMB
GMB

Reputation: 222412

You can do this with window functions only:

select 
    id, 
    country,
    game,
    game_records records,
    date max_date,
    dense_rank() over(order by country_records desc) rnk
from (
    select 
        t.*,
        rank() over(partition by id, country, game order by date desc) rn,
        sum(records) over(partition by id, country, game) as game_records,
        sum(records) over(partition by country) country_records
    from mytable t
) t
where rn = 1
order by rnk, records

Thie inner query ranks records having the same (id, country, game) by descending date, and computes window sums for the same (id, country, game) partition and for the partition made of all country records.

Then, the outer query filters on the top record in each of the first patition (this gives us the maximum date), and ranks by total records per country.

This demo on DB Fiddle with your sample data returns:

id | country | game         | records | max_date            | rnk
-: | :------ | :----------- | ------: | :------------------ | :--
 2 | usa     | wow          |      10 | 01/01/2019 00:00:00 | 1  
 2 | usa     | wakfu        |      15 | 01/01/2019 00:00:00 | 1  
 2 | usa     | clash royale |      30 | 01/01/2019 00:00:00 | 1  
 2 | germany | dofus        |       9 | 01/01/2019 00:00:00 | 2

Upvotes: 1

Related Questions