题解 | #窗口函数 重点：查询每天刷题最多的前二用户#

#错误示范
# select
#     `user_id`,
#     `date`,
#     sum(`pass_count`) over as pass_count
# from
#     `questions_pass_record`
# group by
#     `user_id`
# order by
#     `pass_count`
# limit
#     2


#分组（一对一可以用正常group（一组对应一条数据），一对多就用（一组对应多条数据））
#没有考虑到一个人一天可以刷多门课---不过思路是正确的
# select 
#             date,
#             user_id,
#             pass_count
# from
#       (   
#         select
#             date,
#             user_id,
#             pass_count,
#             row_number() over (
#                 partition by
#                     date
#                 order by
#                     pass_count desc
#             ) as row_num
#         from
#             questions_pass_record
#       )  as t
#  where
#    row_num <= 2;


# select 
#             date,
#             user_id,
#             pass_count
# from
#       (   
#         select
#             date,
#             user_id,
#             pass_count,
#             row_number() over (
#                 partition by
#                     date
#                 order by
#                     pass_count desc
#             ) as row_num
#         from
#             questions_pass_record
#       )  as t
#  where
#    row_num <= 2;

#因为一个人要刷不同的题，所以要先通过聚合计算出总刷题数。因为要计算一个人的刷题数，所以要对user_id进行分组
#  select user_id, date, sum(pass_count) as pass_count
#         from questions_pass_record
#         group by user_id, date

select 
    date,
    user_id,
#   b.row,
    pass_count
from
(
    select  date,
         user_id,
         pass_count,
         dense_rank() over(partition  by `date` order by `pass_count` desc) as `row`
    from
        (select user_id, date, sum(pass_count) as pass_count
         from questions_pass_record
         group by user_id, date) as a
)  as b 
where b.row<=2