I have this query that produced the table below.

select season,
       guildname,
       count(guildname) as mp_count, 
       (count(guildname)/600::float)*100 as grank
from mp_rankings
group by season, guildname
order by grank desc

I wanted to create a new column that calculates the percentage difference between the two seasons using identical guildnames. For example:

The resulting table will only show the current season (which is the highest season value, 10 in this case) and adds a new column prev_season_percent_diff, which is the current season's grank minus the previous season's grank.

How can I achieve this?

CodePudding user response：

Use a Common Table Expression ("CTE") for the grouped result and join it to itself to calculate the difference to the previous season:

with summary as (
  select
    season,
    guildname,
    count(*) as mp_count, -- simplified equivalent expression
    count(*)/6 as grank -- simplified equivalent expression
  from mp_rankings
  group by season, guildname
)
select
  a.season,
  a.guildname,
  a.mp_count,
  a.grank,
  a.mp_count - b.mp_count as prev_season_percent_diff
from summary a
left join summary b on b.guildname = a.guildname
  and b.season = a.season - 1
where a.season = (select max(season) from summary)
order by a.grank desc

If you actually want a % in the result, concatenate a % to the difference calculation.