题解 | #统计各个部门的工资记录数#
牛客每个人最近的登录日期(五)
http://www.nowcoder.com/practice/ea0c56cd700344b590182aad03cc61b8
解本题需要两个表：新用户表、次日留存表
怎么把两个表关联起来求留存率？
我的想法是，在原明细的基础上，加两个字段①新用户：判断每条登陆日志的用户是否是新用户（1是，0不是）；②次日登陆：判断该日登陆的用户第二天是否再次登陆（1是，0不是）。因此只要对“新用户”求和，作为留存率的分母，对“次日登陆”求和作为留存率的分子就可以求出留存率。
- 新用户
-
select user_id,min(date) as "min" from login group by user_id
- 关联次日登陆用户
-
left join ( select user_id,date from login ) as p on p.user_id=lg.user_id and p.date=date_add(lg.date,interval 1 day)
整合上述两表，并在原login表上，加上两个为0-1变量的字段
select lg.*,
case when p2.min=lg.date then 1 else 0 end "new",
case when p.date is not null then 1 else 0 end "after"
from login as lg
left join
(
select user_id,date
from login
) as p on p.user_id=lg.user_id and p.date=date_add(lg.date,interval 1 day)
left join
(
select user_id,min(date) as "min"
from login
group by user_id
) as p2 on p2.user_id=lg.user_id
在select上计算留存率，需要应用ifnull函数
select distinct p3.date,ifnull(round(sum(p3.after)/sum(p3.new),3),0.000) as "p" from ( select lg.*,case when p2.min=lg.date then 1 else 0 end "new",case when p.date is not null then 1 else 0 end "after" from login as lg left join ( select user_id,date from login ) as p on p.user_id=lg.user_id and p.date=date_add(lg.date,interval 1 day) left join ( select user_id,min(date) as "min" from login group by user_id ) as p2 on p2.user_id=lg.user_id ) as p3 group by p3.date
