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

/*首先需要知道留存客户的id
然后按照每天分组
留存客户的id/分组内去重后的客户id就是每天的留存率
加上两个标签，一个是当日用户总数，一个是次日留存用户总数
注意：有些客户次日不上 后日上 不是简单判定日期总数大于多少就行
所以需要客户分组加上排序标签，检验排序第一和排序第二是否只差一天
大于1天的就是非留存客户
因为是按照每一天计算的，后面表连接后，可以试着算
注意：一天多个不同设备登录


用户登录的最小日期就是第一登陆日
然后选出连续两日有登录的客户


可以先对login去重防止每天用不同设备登录的用户 */

with a1 as
(
    select
    date_add(min(l.date),interval 1 day) as d1
    ,min(l.date) as reserve
    ,l.user_id as u1
    from
    login l
    group by l.user_id
)   #每个用户第一个登陆日与隔天,没有id


,a11 as
(

    select 
    *
    from (
    select
    *
    ,dense_rank()over(partition by l.user_id order by l.date asc) as t_tank
    from login l
    ) as t1
    where t1.t_tank=1

)
 #每个用户第一个登陆日,有id

,a2 as
(select
*
,date_sub(l.date,interval 1 day) as first_day
from login l
where (l.user_id,l.date) in (select a1.u1,a1.d1 from a1 ))#留存客户，以及对应的第一天登陆日期

#题目是只对那日的新用户进行计算，从那日出现过的客户，后面就不再列入计算，所以只需要提取一张客户登录最小日期表
#找出非留存客户，这个是列入分母计算的，留存客户除了第一天的数据，其余天数的登陆数会影响计算



# 找出所有客户的第一登陆日，这是分母，与留存客户的第一登陆日，这是分子，分母包含分子
select
l.date as date
,ifnull(round(count(a3.user_id)/count(a11.user_id),3), 0)as p
from login l
left join
a11
on l.id=a11.id
left join 
(select
*
from login l
where (l.user_id,l.date) in (select user_id, first_day from a2)
# where l.user_id in (select user_id from a2)  这样写，会导致留存客户在非第一登陆日也出现了，导致结果错误以日期分组的留存率计算错误
) as a3
on a11.id=a3.id
group by l.date
#别的日期的留存客户在此日期不列入计算---难点

整理整理再发一次