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

 #用union查找每日的用户活跃记录(因为题目里说"跨天算两条记录")
#找出每个用户的首次活跃日期
#连接表准备查询每日新增和新增占比
#算
with
    t1 as (
        select
            uid,
            date(in_time) as tm
        from
            tb_user_log
        union
        select
            uid,
            date(out_time) as tm
        from
            tb_user_log
    ),
    t2 as (
        select
            uid,
            tm,
            rank() over (
                partition by
                    uid
                order by
                    tm
            ) rk
        from
            t1
    ),
    t3 as (
        select
            uid,
            tm first_tm
        from
            t2
        where
            rk = 1
    ),
    t4 as (
        select
            t1.uid,
            t1.tm,
            t3.first_tm
        from
            t1
            join t3 using (uid)
    )
select
    tm,
    count(*) dau,
    round(
        sum(
            case
                when tm = first_tm then 1
                else 0
            end
        ) / count(*),
        2
    ) as uv_new_ratio
from
    t4
group by
    tm
order by
    tm