题解 | #2021年11月每天新用户的次日留存率#
2021年11月每天新用户的次日留存率
https://www.nowcoder.com/practice/1fc0e75f07434ef5ba4f1fb2aa83a450
# 注意点:1 统计时间:2021年11月 # 2 如果in_time-进入时间和out_time-离开时间跨天了,在两天里都记为该用户活跃过 # 3 结果按日期升序 # 由于跨日计算为两天均活跃,所以将in_time 和 out_time 通过 union 函数合并,并通过 distinct去重,此时不要过滤登录时间。代码如下: with t1 as ( select distinct uid, date (in_time) sin_date from tb_user_log union select distinct uid, date (out_time) sin_date from tb_user_log ), # 统计时间:2021年11月,需要将不是11月的新用户通过 where 过滤掉,注意要过滤用户最小的登录时间,而不是仅过滤用户的登录时间,否则10月的老用户在11月份登录时,容易被误记为新用户。 # 过滤后的uid就是当日的新用户数量。 t2 as ( select min(sin_date) dt, uid total_id from t1 group by uid having date_format (min(sin_date), '%Y-%m') = '2021-11' ) # 计算 次日留存的用户数量,通过 datediff 计算出最早登录时间和下次登录时间差值为1的用户,按照最早登录时间分组并计算新用户和留存用户的数量。按照日期升序排序。 select dt, round(count(uid) / count(total_id), 2) uv_left_rate from t2 left join t1 on t1.uid = t2.total_id and datediff (sin_date, dt) = 1 group by dt order by dt;