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

#每个日期新用户是分母，所以要剔除每个日期的老用户。次日留存的新用户是分子。2.0的日子也要输出，所以分组要以全日期进行分组

with fir as #每个用户的第一登陆日=剔除每日的老用户，纯正的新用户登陆表
(
select 
distinct user_id #用distinct 是防止新用户在第一天用不同设备登录的情况
,min(date) as first_day
from login 
group by user_id
)
, maintain_sec as #次日留存的新用户以及登录时间
(
select
*
from login 
where (user_id, date) in (select user_id, date_add(first_day, interval 1 day) from fir)
)
,maintain_fir as #可作为分子的数据，次日留存的新客户,与fir日期相同了
(
select
*
from fir
where user_id in (select user_id from maintain_sec)
)
,pure_datelist as #无重复的原日期列
(
select
distinct date
from login 
)
,fenzi as #算出每日分子数
(
select
first_day
,count(first_day) as cnt
from 
maintain_fir
group by first_day
)
,fenmu as #算出每日分母数
(
select
first_day
,count(first_day) as cnt
from 
fir
group by first_day
)


select
p.date
,case 
 when fm.cnt is null then 0.000 #若分母为0，则分子必为0，0/0没有计算意义
 when fz.cnt is null then 0.000 #若分子为0,无论分母为何物，结果为0,否则round(null/a)=round(null)=null
 when fm.cnt is not null then round(fz.cnt/fm.cnt,3)
end as p
from pure_datelist p left join fenmu fm
on p.date = fm.first_day
left join fenzi as fz
on p.date=fz.first_day
#group by p.date 在创建cte表的过程中，date已经去重复了，对应的分子和分母已经算好了
order by p.date asc

/*此题的坑点：
1.计算次日留存率存在3种情况
  (1. 分母有数，分子有数（某日有新用户，且其中有留存）
  (2. 分母有数，分子无数（某日有新用户，但没留存）
  (3. 分母无数，分子无数（某日没有新用户，自然也没留存）

2.null在四则计算里面的情况
   (1.null参与的四则计算，结果都为null
   (2.round(null)为null

1.和2.结合，在单独计算分子数和分母数的cte中，有些日期是不参与计算的，与纯日期表进行连接后自然就会出现null值，这时候就会导致最终的输出运算过程中有null的出现，从而导致最终的输出结果有null，所以需要出现case when 
*/