题解 | #每天的日活数及新用户占比#
每天的日活数及新用户占比
https://www.nowcoder.com/practice/dbbc9b03794a48f6b34f1131b1a903eb
select tb_active_user.dt,count(tb_active_user.uid) dau,round(count(tb_register.uid)/count(tb_active_user.uid),2) uv_new_ratio from( (select uid,date(in_time) dt from tb_user_log group by dt,uid union select uid,date(out_time) dt from tb_user_log group by dt,uid)tb_active_user left join( select uid,min(date(in_time)) dt from tb_user_log group by uid )tb_register on tb_active_user.uid = tb_register.uid and tb_active_user.dt = tb_register.dt ) group by dt order by dt
构造用户活跃表与构造的用户注册表左连接,然后根据用户活跃表的日期聚合和排序,count(tb_active_user.uid)即为当日活跃人数,count(tb_register.uid.uid)为新增人数