题解 | #牛客每个人最近的登录日期(六)#

# 先根据题目给出思路：
# （1）查询每个id的各个登录日期（忽略不同客户端登录的记录）
select user_id, `date`
from login 
group by user_id, `date`
# （2）统计各个id各个日期的通过题目数量
select t1.user_id, t1.`date`, ifnull(`number`, 0) `number`
from (
    select user_id, `date`
    from login 
    group by user_id, `date`
) t1
join passing_number p
on t1.user_id = p.user_id and t1.`date` = p.`date` 
# （3）使用窗口函数统计各id各个日期的累计通过题目数量
select *, sum(`number`)over(partition by user_id order by `date`) ps_num
from (
    select t1.user_id, t1.`date`, ifnull(`number`, 0) `number`
    from (
        select user_id, `date`
        from login 
        group by user_id, `date`
    ) t1
    join passing_number p
    on t1.user_id = p.user_id and t1.`date` = p.`date` 
) t2
# （4）连接（1）（2）（3）步骤得出的统计表并与user表连接填入用户名
select u.name u_n, `date`, ps_num
from (
    select *, sum(`number`)over(partition by user_id order by `date`) ps_num
    from (
        select t1.user_id, t1.`date`, ifnull(`number`, 0) `number`
        from (
            select user_id, `date`
            from login 
            group by user_id, `date`
        ) t1
        join passing_number p
        on t1.user_id = p.user_id and t1.`date` = p.`date` 
    ) t2
) t3
join user u
on t3.user_id = u.id
order by `date`, u_n;

# 完整代码如下：
select u.name u_n, `date`, ps_num
from (
    select *, sum(`number`)over(partition by user_id order by `date`) ps_num
    from (
        select t1.user_id, t1.`date`, ifnull(`number`, 0) `number`
        from (
            select user_id, `date`
            from login 
            group by user_id, `date`
        ) t1
        join passing_number p
        on t1.user_id = p.user_id and t1.`date` = p.`date` 
    ) t2
) t3
join user u
on t3.user_id = u.id
order by `date`, u_n;