题解 | #每天的日活数及新用户占比#
每天的日活数及新用户占比
https://www.nowcoder.com/practice/dbbc9b03794a48f6b34f1131b1a903eb
明确题意:
统计每天的日活数及新用户占比
问题分解:
查询活跃用户表:select uid,date(in_time) dt from tb_user_log union select uid,date(out_time) dt from tb_user_log
计算用户注册时间:min(dt) over(partition by uid)
按天分组:group by dt
计算日活数:count(distinct uid)
计算日活中新用户数:sum(if(register_dt = dt, 1, 0))
计算新用户占比:new_cnt / dau
细节问题:
新用户占比保留2位小数:round(x,2)
结果按日期升序排序:order by dt
select dt, dau, round(new_cnt / dau, 2) uv_new_ratio from( select dt, count(distinct uid) dau, sum(if(register_dt = dt, 1, 0)) new_cnt from( select uid, dt, min(dt) over(partition by uid) register_dt from( select uid, date(in_time) dt from tb_user_log union select uid, date(out_time) dt from tb_user_log ) t1 ) t2 group by dt ) t3 order by dt