这道题目要求我们计算新用户在首次登录后的次日留存率。我们要做的事情如下：

1. 确定总体问题

我们需要从登录记录表中提取数据，计算每个新用户在首次登录后的第二天是否再次登录的概率。并保留三位小数。留存率是指在首次登录后的第二天再次登录的用户占总新用户的比例。

2. 分析关键问题

  • 确定每个用户的首次登录日期：通过分组和聚合函数MIN来获取每个用户的首次登录日期。
  • 检查次日登录情况：通过连接操作检查每个用户在首次登录后的第二天是否再次登录。
  • 计算留存率：计算次日登录的用户数占总新用户数的比例，并保留三位小数。

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

步骤1：确定每个用户的首次登录日期

我们使用GROUP BY和MIN函数来获取每个用户的首次登录日期：

select user_id, min(date) as date from login group by user_id
  • MIN(date) AS date: 获取每个用户的首次登录日期。
步骤2：检查次日登录情况

我们通过INNER JOIN连接操作检查每个用户在首次登录后的第二天是否再次登录：

join
    login l2
    on l1.user_id = l2.user_id
  • JOIN: 连接l1和l2表，检查用户在首次登录后的第二天是否再次登录。
步骤3：计算留存率

我们使用CASE语句和SUM函数计算次日登录的用户数，并除以总新用户数得到留存率：

select
    round(sum(case when l2.date = date_add(l1.date, interval 1 day) then 1 else 0 end) / count(distinct l1.user_id), 3) as p
  • CASE WHEN l2.date = DATE_ADD(l1.date, INTERVAL 1 DAY) THEN 1 ELSE 0 END: 检查用户是否在首次登录后的第二天再次登录。
  • DATE_ADD(l1.date, INTERVAL 1 DAY): l1.date的日期加一天
  • ROUND(..., 3): 保留三位小数。

完整代码

select
    round(sum(case when l2.date = date_add(l1.date,interval 1 day) then 1 else 0 end)/count(distinct l1.user_id),3) as p
from
    (select user_id,min(date) as date from login group by user_id)l1
join
    login l2
    on l1.user_id = l2.user_id