Calculate dense rank by sum-CodePudding

Sample data:

student	marks	subject
stud1	100	sub1
stud1	400	sub2
stud1	500	sub3
stud2	200	sub1
stud2	700	sub2
stud2	800	sub3
stud2	900	sub4
stud3	300	sub1
stud3	600	sub2
stud4	1000	sub1

Trying to partition by student and order by sum of marks like below using dense_rank(). There are multiple other columns and since the data is big, trying to avoid any joins here.

Expected output:

student	marks	subject	ds_rnk
stud1	100	sub1	2
stud1	400	sub2	2
stud1	500	sub3	2
stud2	200	sub1	1
stud2	700	sub2	1
stud2	800	sub3	1
stud2	900	sub4	1
stud3	300	sub1	3
stud3	600	sub2	3
stud4	1000	sub1	2

Thanks in advance!

CodePudding user response：

You can calculate sum and dense_rank in two stages:

with cte as (
    select t.*, sum(marks) over (partition by student) as sum_marks
    from t
)
select cte.*, dense_rank() over (order by sum_marks desc) as dense_rankk
from cte