I have a table with rows like this:

I want to be able to get the points per guild based on their player's rank as follows:

• rank >=1 && rank <= 150 : 4
• rank >=151 && rank <= 300 : 2
• rank >=301 && rank <= 600 : 1

and would like the resulting table to be:

This is what I have so far:

WITH mpguildpoints (
  guildname,
  points,
  season
) AS (
    select mp_rankings.guildname,
    case when mp_rankings.rank >= 1 AND mp_rankings.rank <= 150 then 4
    when mp_rankings.rank >= 151 AND mp_rankings.rank <= 300 then 2
    when mp_rankings.rank >= 300 AND mp_rankings.rank <= 600 then 1
    end as points,
    season
    from mp_rankings
    group by mp_rankings.guildname,mp_rankings.rank,mp_rankings.season
    order by points desc, season desc
)

select guildname, count(points), sum(points) as totalpoints from mpguildpoints
where season = 10
group by guildname,points
order by totalpoints desc

CodePudding user response：

Use range operators, aggregation filters, and window functions:

with pointcalc as (
  select guildname, 
         case
           when int4range(1, 150, '[]')   @> rank then 4
           when int4range(151, 300, '[]') @> rank then 2
           when int4range(301, 600, '[]') @> rank then 1
           else 0
         end as points
    from rankings
), counts_sum as (
  select guildname, 
         count(1) filter (where points = 4) as rank1to150,
         count(1) filter (where points = 2) as rank151to300,
         count(1) filter (where points = 1) as rank301to600,
         sum(points) as sumofpoints
    from pointcalc
   group by guildname
)
select *, 
       rank() over (order by sumofpoints desc)
  from counts_sum
 order by rank;

db<>fiddle here

CodePudding user response：

Put each condition in a CASE expression and perform conditional aggregation:

with a (guildname, playerrank, name, season) as (
  select *
  from(values
('GuildOne', 1, 'Player1', 10),
('GuildOne', 3, 'Player2', 10),
('GuildOne', 30, 'Player3', 10),
('GuildTwo', 7, 'Player4', 10),
('GuildTwo', 9, 'Player5', 10),
('GuildTwo', 31, 'Player6', 10),
('GuildThree', 63, 'Player7', 10),
('GuildThree', 393, 'Player8', 10),
('GuildThree', 99, 'Player9', 10),
('GuildOne', 216, 'Player10', 10)
  ) as t
)
select
  b.*,
  dense_rank() over(order by score desc) as rank_
from (
select
  guildname,
  /*Count per group    */
  count(1) filter(where playerrank between 1 and 150) as rank1to150, 
  count(1) filter(where playerrank between 151 and 300) as rank151to300, 
  count(1) filter(where playerrank between 301 and 600) as rank300up, 
  sum(
    case
      /*Case stops on the first TRUE branch*/
      when playerrank <= 150 then 4
      when playerrank <= 300 then 2
      when playerrank <= 600 then 1
    end
  ) as score
  from a
group by 1
) as b

guildname  | rank1to150 | rank151to300 | rank300up | score | rank_
:--------- | ---------: | -----------: | --------: | ----: | ----:
GuildOne   |          3 |            1 |         0 |    14 |     1
GuildTwo   |          3 |            0 |         0 |    12 |     2
GuildThree |          2 |            0 |         1 |     9 |     3

db<>fiddle here

CodePudding user response：

You are almost near on your query, just need to do joins for the ranking.

select t1.guildname
    , t2.ct as rank1to150
    , t3.ct as rank151to300
    , t4.ct as rank301up
    , t1.totalpoints
    , t1.ct as "rank"
from 
    (select guildname, count(points) ct, sum(points) as totalpoints from mpguildpoints
    where season = 10
    group by guildname, points) t1
left join
    (select guildname, count(1) ct from mp_rankings
    where season = 10 and rank between 1 and 150
    group by guildname) t2 on t2.guildname = t1.guildname
left join
    (select guildname, count(1) ct from mp_rankings
    where season = 10 and rank between 151 and 300
    group by guildname) t3 on t3.guildname = t1.guildname   
left join
    (select guildname, count(1) ct from mp_rankings
    where season = 10 and rank > =301 
    group by guildname) t4 on t4.guildname = t1.guildname   
order by t1.rank asc