题解 | #每天的日活数及新用户占比#
每天的日活数及新用户占比
https://www.nowcoder.com/practice/dbbc9b03794a48f6b34f1131b1a903eb
select dt ,count(distinct au.uid) AS dau ,round(count(distinct nu.uid)/count(distinct au.uid),2) AS uv_new_ratio from( select uid ,date(in_time) AS dt from tb_user_log union select uid ,date(out_time) AS dt from tb_user_log )au left join ( select uid ,date(min(in_time)) AS fd from tb_user_log group by uid )nu on au.dt = nu.fd and au.uid = nu.uid group by dt order by dt asc
1、日活用户 left join 新户
2、union可以去重,所以uid,in_time union uid,out_time可以把in_time和out_time跨天的uid作为两天的活跃用户给筛选出来。