题解 | #每天的日活数及新用户占比#

明确题意：

统计每天的日活数及新用户占比

问题分解：

查询活跃用户表：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