题解 | #每天的日活数及新用户占比#
每天的日活数及新用户占比
https://www.nowcoder.com/practice/dbbc9b03794a48f6b34f1131b1a903eb
思路:
- 分别创建用户新增表活和跃表,分别得到每天的新增用户数和活跃用户数
- 通过日期联立两表,计算活跃率
with t1 as( select uid,date(in_time) dt from tb_user_log union select uid,date(out_time) dt from tb_user_log ), -- 活跃表 t2 as( select uid, min(dt) as dt2 from t1 group by uid ) -- 新增表 select t1.dt, count(t1.uid) as dau, round(count(t2.uid)/count(t1.uid),2) as uv_new_ratio from t1 left join t2 on t1.uid=t2.uid and t1.dt=t2.dt2 group by dt order by dt