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

# 问题：统计每天的日活数及新用户占比

select
# 统计每天的日活树及新用户占比
    dt,
    count(distinct uid) dau,
    round(
        sum(if (date (first_log) = dt, 1, 0)) / count(distinct uid),
        2
    ) uv_new_ratio
from
    (
        select
            # 每个用户的初次注册时间，活跃时间，去重
            distinct uid,
            first_log,
            date (time) dt
        from
            (
                # 每个用户的初次注册时间
                select
                    uid,
                    min(in_time) first_log
                from
                    tb_user_log
                group by
                    uid
            ) as tb_log
            right join (
                # 每个用户的活跃时间
                select
                    *,
                    in_time time
                from
                    tb_user_log
                union
                select
                    *,
                    out_time time
                from
                    tb_user_log
            ) as tb_user_log using (uid)
    ) as tb_log_information
group by
    dt
order by
    dt