题解 | #统计各个部门的工资记录数#

解本题需要两个表：新用户表、次日留存表

怎么把两个表关联起来求留存率？

我的想法是，在原明细的基础上，加两个字段①新用户：判断每条登陆日志的用户是否是新用户（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