题解 | #每天的日活数及新用户占比#
每天的日活数及新用户占比
http://www.nowcoder.com/practice/dbbc9b03794a48f6b34f1131b1a903eb
select dt,count(distinct uid) dau,
round(sum(case when (uid,dt) in (select uid,date(min(in_time)) from tb_user_log group by uid) then 1 else 0 end)/
count(distinct uid),2) uv_new_ratio
from(
select uid,date(in_time) dt from tb_user_log
union
select uid,date(out_time) dt from tb_user_log
)a
group by dt
order by dt
round(sum(case when (uid,dt) in (select uid,date(min(in_time)) from tb_user_log group by uid) then 1 else 0 end)/
count(distinct uid),2) uv_new_ratio
from(
select uid,date(in_time) dt from tb_user_log
union
select uid,date(out_time) dt from tb_user_log
)a
group by dt
order by dt