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

这道题目要求我们计算每个日期的新用户次日留存率。我们要做的事情如下：

1. 确定总体问题

我们需要计算每个日期的新用户次日留存率，次日留存率是指在某个日期首次登录的新用户中，有多少用户在次日再次登录。我们需要从登录记录表中提取数据，使用窗口函数ROW_NUMBER来标记每个用户的首次和次次登录日期，然后计算次日留存率。并保留小数点后三位。

2. 分析关键问题

• 标记用户的首次和次次登录日期：使用ROW_NUMBER窗口函数为每个用户的登录记录分配排名，标记出首次和次次登录日期。
• 计算次日留存率：通过CASE语句和SUM函数计算次日留存率。
• 格式化输出：保留小数点后三位，并按日期升序排列输出结果。

3. 解决每个关键问题的代码及讲解

步骤1：标记用户的首次和次次登录日期

我们使用ROW_NUMBER窗口函数为每个用户的登录记录分配排名，标记出第2次登录日期：

from(
    select 
        user_id, date,
        row_number() over(partition by user_id order by date asc) as rk
    from login
) s 
where rk = 2

• ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY date ASC) AS rk：为每个用户的登录记录分配排名，按日期升序排列。rk = 2表示第2次登录日期。
• 注意本题是输出第一天的数据但要判断第二天是否登录。

步骤2：计算次日留存率

我们使用CASE语句和SUM函数计算次日留存率：

case
    when sum(
        case
            when l.date < l1.date then 1 else 0
        end
    ) = 0 then 0.000
    else round(
        sum(
            case
                when l1.date = date_add (l.date, interval 1 day) then 1 else 0
            end
        ) / sum(
            case
                when l.date < l1.date then 1 else 0
            end
        ), 3
    )
end as p

• SUM(CASE WHEN l.date < l1.date THEN 1 ELSE 0 END): 计算某日期的新用户数。
• SUM(CASE WHEN l1.date = DATE_ADD(l.date, INTERVAL 1 DAY) THEN 1 ELSE 0 END): 计算次日登录的新用户数。
• ROUND(..., 3): 保留小数点后三位。
• when sum( case when l.date < l1.date then 1 else 0 end ) = 0 then 0.000 :考虑分母等于0的情况

完整代码

select
    l.date,
    case
    when sum(
        case
            when l.date < l1.date then 1 else 0
        end
    ) = 0 then 0.000
    else round(
        sum(
            case
                when l1.date = date_add (l.date, interval 1 day) then 1 else 0
            end
        ) / sum(
            case
                when l.date < l1.date then 1 else 0
            end
        ), 3
    )
end as p
from
    login l
left join (
    select
        user_id,
        date
    from(
        select 
            user_id, date,
            row_number() over(partition by user_id order by date asc) as rk
        from login
    ) s 
    where rk = 2
) l1 on l.user_id = l1.user_id
group by l.date;