思路拆解

1、判断 每个日期当天 「新用户的次日留存率」

2、新用户次日留存率=当天的新用户数/第二天的新用户数

做法拆解v1（没做出来）

1、新增一列标注 新、老。当天的新用户数 count（ case when is_new='新' ）

2、取所有新用户的id as new_id

3、第二天的新用户数：date=第二天,count(user_id in 当天新用户new_id)

做法拆解v2（做出来了）

1、计算当天登录的新用户数

1）按照date = min(date) 查询出 新用户id和首次登录日期

2）给表加上一列 是否新老用户is_new

3）sum(is_new)

2、计算 新用户第二天还登录的用户数

1）聚合两个login表，选出 同一个用户 前后两天的登录信息（左4列+右4列）

2）筛出 新用户的id和首次登录日期（同一行的左4列）

3）根据2） 筛出第二天的信息 （同一行的右4列），count()

3、将1和2的子查询left join，select 2的结果/1的结果

select 
    m.date,
    round(ifnull((n.count_new2/m.count_new),0),3) as p

from(
select
    a.date,
    sum(a.is_new) as count_new #当天新用户的个数
from 
(
    select 
        l2.id,
        l2.user_id,
        l2.date,
        case when l2.date=l1.min_date then 1 else 0 end as is_new
        # 聚合表之后，给表新增一列is_new，标注是否新用户
    from 
    (
        select user_id, min(date) as min_date
        from login
        group by user_id
        # 筛选出 用户user_id 及其首次登录时间
    )l1
    right join login l2 on l2.user_id=l1.user_id

)a
group by a.date
)m
left join(
# 第二天登录的新用户数量
select 
    l3.date,
    count(l4.user_id) as count_new2
from login l3
    left join login l4 on l3.user_id=l4.user_id and 
l4.date = date_add(l3.date, interval 1 day)
#  聚合表：同一个用户，当天+第二天的登录信息。如果没有，则右边4行 无
where (l3.user_id,l3.date) in (
    select user_id,min(date) as min_date
    from login
    group by user_id
)  # 这一步是确保 筛选出新用户 ,但用where 会只筛选出有新用户登录的日子
group by l3.date
)n
on m.date=n.date
group by m.date
order by m.date asc

做法拆解v2的简化版

打印l3和l4的聚合表后看，其实可以计算新用户数量，因此尝试简化版

1、新用户首日登录数量= 同一个用户id下，date=min_date时，count(人数)

2、新用户次日登录数量= 满足1且第二日数据is not null （即有登录）

select
    l3.date,
    round(ifnull(sum(
        case when (l3.user_id,l3.date) in 
            (
            select user_id,min(date) 
            from login
            group by user_id
            )
            and l4.date is not null
        then 1
        else 0 
        end
    ) /
        sum(case when (l3.user_id,l3.date) in 
            (
            select user_id,min(date) 
            from login
            group by user_id
            )
        then 1
        else 0 
        end
        ),0),3) as p
from login l3
    left join login l4 on l3.user_id=l4.user_id and 
l4.date = date_add(l3.date, interval 1 day)
group by l3.date