How to calculate sum for current value and all the values below it-CodePudding

I have got a table that contains information about every user's level for specific version. Let's suppose its name is user_level_advanced.

version	user_id	level
0.9.3	1	2
0.9.5	2	3
0.9.3	3	4
0.9.3	4	5

and i want to count how many times each level is advanced so that every user should be considered for the levels below their current level. For the table above, the result should be like this.

version	level	advanced_count
0.9.3	1	3
0.9.3	2	3
0.9.3	3	2
0.9.3	4	2
0.9.3	5	1
0.9.5	1	1
0.9.5	2	1
0.9.5	3	1

with user_level_advanced as(
    select 
        "0.9.3" as version, 1 as user_id, 2 as level_advanced_max
        union all
       select  "0.9.5" as version, 2 as user_id, 3 as level_advanced_max
        union all 
        select "0.9.3" as version, 3 as user_id, 4 as level_advanced_max
        union all 
        select "0.9.3" as version, 4 as user_id, 5 as level_advanced_max
),
user_grouped_by as
(
    select version, level_advanced_max, count(*) as level_advanced_count 
    from user_level_advanced 
    group by version, level_advanced_max
)

select  version,
        level_advanced_max,
        sum(level_advanced_count) over(partition by version order by level_advanced_max asc rows between current row and unbounded following)
from user_grouped_by

I use this query for calculating it but it has a flaw that if one level is missing inside the user_level_advanced table it will also be missing for the result table as well. Thanks for any help.

version	level	advanced_count
0.9.3	2	3
0.9.3	4	2
0.9.3	5	1
0.9.5	3	1

CodePudding user response：

Consider below approach

select version, level, count(*) advanced_count
from user_level_advanced, 
unnest(generate_array(1, level_advanced_max)) level
group by version, level

if applied to sample data in your question - output is

CodePudding user response：

You can using a cross join generate all possible (level, version) couples, then join it to your table to get the expected result:

with recursive levels as
(select 1 as level
union all
select level   1 from levels
where level < (select max(level) from user_level_advanced)),
versions as
(select distinct version from user_level_advanced),
cte as
(select * from versions cross join levels)
select cte.version, cte.level, count(*)
from cte inner join user_level_advanced ula
on ula.level >= cte.level and ula.version = cte.version
group by cte.version, cte.level

Fiddle