题解 | #查询每天刷题通过数最多的前二名用户id和刷题数#
查询每天刷题通过数最多的前二名用户id和刷题数
https://www.nowcoder.com/practice/b9cc0d5047f94bc0a661c5a0a230b9cd
select t2.date, t2.user_id, t2.sum_pass_count as pass_count
from (select t1.date, t1.user_id, t1.sum_pass_count,
dense_rank() over (partition by t1.date order by t1.sum_pass_count desc) rn-- 不跳数字排名
from
(select date, user_id, sum(pass_count) as sum_pass_count
from questions_pass_record
group by user_id, date) as t1-- 考虑一个用户在同一天内刷了不同题型，所以结果前二的user-id不会是同一个人
) as t2
where t2.rn <= 2
order by t2.date
