题解 | #2021年11月每天新用户的次日留存率#

# 4. 计算2021年11月每天新用户的次日留存率
select first as dt, round(count(uid2)/count(uid1),2) as uv_left_rate from(
# 3. join，选择用户在注册第二天在线的记录
select t1.uid as uid1, t2.uid as uid2, first from(
    # 1. 底表：用户注册表
    select uid, min(date_format(in_time,"%Y-%m-%d")) as first from tb_user_log
    group by uid
    having year(first)=2021 and month(first)=11
) t1
left join (
# 2. 连接表：用户在线表
    (select uid, date_format(in_time,"%Y-%m-%d") as dt from tb_user_log)
    union
    (select uid, date_format(out_time,"%Y-%m-%d") as dt from tb_user_log)
) t2
on t1.uid = t2.uid and datediff(dt,first)=1
) t3
group by dt
order by dt

今天的次日留存率 = 次日活跃的新用户(今天注册) / 新用户(今天注册)

底表：今天注册的新用户（用户注册表）。初次登陆时间

left join：每个用户每日的在线信息（用户在线表）。由于存在跨天在线的情况，不论当日客户登录还是退出都认为客户在当天在线

join condition：uid一致，左连接表的date比底表date加1。

(今天注册的用户match该用户明天登陆信息)，保留每个用户注册第二天的登陆信息

join result：若该用户在first_time的次日活跃（连接表有信息），则连接表的uid非空，否则连接表的uid为空。

**group by result：**底表 sum(t1.uid) group by first 为当日（first）新用户数，连接表的sum(t2.uid) group by first 数为次日（first+1）活跃的新用户数，。

❗️遇到日期罗列或者跨天的情况需要依据开始时间和结束时间拆分数据再UNION

选择UNION或UNION ALL需要考虑数据颗粒度，数据是否会重叠