28. 查询有新注册用户的当天的新用户数量、新用户的第一天留存率

题目需求

从用户登录明细表（user_login_detail）中查询有新注册用户的当天的新用户数量、新用户的第一天留存率。

首次登录算作当天新增，第二天也登录了算作一日留存。

期望结果如下：

first_login （注册时间）	register （新增用户数）	retention <decimal(16,2)> （留存率）
2021-09-21	1	0.00
2021-09-22	1	0.00
2021-09-23	1	0.00
…	…	…

需要用到的表：

用户登录明细表：user_login_detail

user_id(用户id)	ip_address(ip地址)	login_ts(登录时间)	logout_ts(登出时间)
101	180.149.130.161	2021-09-21 08:00:00	2021-09-27 08:30:00
102	120.245.11.2	2021-09-22 09:00:00	2021-09-27 09:30:00
103	27.184.97.3	2021-09-23 10:00:00	2021-09-27 10:30:00

实现一

-- 4）计算出 每一天 新增用户的 留存率
select login_ts as first_login,
       register,
       -- 第二天活跃的用户数
       -- sum(whe),
       cast(sum(whe) / register as decimal(16, 2)) as retention
from (
         select t2.login_ts,     -- 日期A
                t2.register                                           as register,
                t2.user_id,
                t3.day_all_user, -- 日期A+1，活跃的所有用户 user_id
                -- 判断该新增用户在 注册第二天是否活跃。1:活跃, 0: 不活跃
                if(array_contains(t3.day_all_user, t2.user_id), 1, 0) as whe
         from (
                  -- 2） 统计每个日期的新用户数量
                  select login_ts,
                         count(*) over (partition by login_ts) as register,
                         user_id
                  from (
                           -- 1） 统计每个用户的首次登录日期
                           select distinct user_id,
                                           first_value(date_format(login_ts, 'yyyy-MM-dd'))
                                                       over (partition by user_id order by login_ts) as login_ts
                           from user_login_detail
                       ) t1
              ) t2
                  left join
              (
                  -- 3） 统计每个日期 活跃用户的user_id
                  select date_format(login_ts, 'yyyy-MM-dd') login_ts,
                         collect_set(user_id) as             day_all_user
                  from user_login_detail
                  group by date_format(login_ts, 'yyyy-MM-dd')
              ) t3
              -- 日期A = 日期(B-1)。 B=A+1
              on t2.login_ts = date_add(t3.login_ts, -1)
     ) t4
group by login_ts, register;

实现二

补充于2024.02.28 18:20，方法更优。

SELECT
    -- 新用户注册日期
    t2.login_ts                                             AS first_login,
    -- 日期当天的新用户注册数
    t2.register                                             AS register,
    -- 留存率
    -- COUNT(t3.user_id) 统计第二天也登录了的用户数
    CAST(COUNT(t3.user_id) / t2.register AS DECIMAL(16, 2)) AS retention
FROM (
         -- 2） 统计每个日期的新用户数量
         SELECT login_ts,
                COUNT(*) OVER (PARTITION BY login_ts) AS register,
                user_id
         FROM (
                  -- 1） 统计每个用户的首次登录日期
                  SELECT DISTINCT user_id,
                                  FIRST_VALUE(DATE_FORMAT(login_ts, 'yyyy-MM-dd'))
                                              OVER (PARTITION BY user_id ORDER BY login_ts) AS login_ts
                  FROM user_login_detail
              ) t1
     ) t2
         LEFT JOIN
     (
         -- 登录明细去重
         SELECT DISTINCT DATE_FORMAT(login_ts, 'yyyy-MM-dd') AS login_ts,
                         user_id
         FROM user_login_detail
     ) t3
     ON t2.user_id = t3.user_id AND t2.login_ts = DATE_ADD(t3.login_ts, -1)
GROUP BY t2.login_ts, t2.register;

题目来源

http://practice.atguigu.cn/#/question/28/desc?qType=SQL