题解 | 查询每天刷题通过数最多的前二名用户id和刷题数

/*查询思路
1.考虑到每个用户每天不止刷一类题目，因此首先查询每个用户每天刷各类题目的总数 p_count，按user_id和date分组，得到临时表t1
2.对临时表t1按照题目要求，以date分组并按照p_count排序，得到临时表t2
3.对临时表t2按照题目要求，过滤rn前两名的用户答题信息
*/
select t2.date,t2.user_id,t2.p_count as pass_count
from(
select t1.date,t1.user_id,t1.p_count,dense_rank() over (partition by t1.date order by p_count desc) rn 
from (
    select date,user_id,sum(pass_count) as p_count
from questions_pass_record
group by user_id,date) as t1
) as t2
where t2.rn between 1 and 2
order by t2.date