SQL/Presto: how to rank within a subgroup of each group

Question

I have a table like the following:

group_id  sub_group_id   user_id score  time
1          a1            ann      1     2019
1          a1            bob      1     2020
1          a2            cat      0     2020
2          b1            dan      0     2019
2          b1            eva      0     2019
2          b1            ed       1     2020
2          b2            liz      1     2020

i want to rank user_id within subgroup of each group by the score and then by time (earlier better) each user_id gets. so the desired output is

group_id  sub_group_id   user_id score   time  rank
1          a1            ann      1      2019  1
1          a1            bob      1      2020  2
1          a2            cat      0      2020  1
2          b1            dan      0      2019  1
2          b1            eva      0      2019  1
2          b1            ed       1      2020  2
2          b2            liz      1      2020  1

Answer 1

Use rank():

select t.*,
       rank() over (partition by group_id, sub_group_id order by score desc, time) as ranking
from t;

Actually, I'm not sure if higher scores are better than lower ones, so you might want score asc.