题解 | #每天的日活数及新用户占比#
每天的日活数及新用户占比
https://www.nowcoder.com/practice/dbbc9b03794a48f6b34f1131b1a903eb
select dt ,count(uid) as dau ,round(sum(if(rnk = "1",1,0))/count(uid),2) as uv_new_ratio from ( -- 使用rank窗口函数对每个用户对应的日期进行排序，第一次出现的为新用户 select uid, dt ,rank() over(partition by uid order by dt) as rnk from ( -- 跨天均计入活跃，需要将用户的进入时间和离开时间进行合并处理 select uid ,date(in_time) as dt from tb_user_log union select uid ,date(out_time) as dt from tb_user_log ) t1 ) t2 group by dt order by dt
