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

--- mySQL

WITH
    tb as (
        select
            uid,
            date (in_time) as dt
        from
            tb_user_log
        union
        select
            uid,
            date (out_time) as dt
        from
            tb_user_log
    )
select
    t3.dt,
    count(DISTINCT t3.uid) as dau,
    round(sum(t3.uv_new) / count(DISTINCT t3.uid), 2) as uv_new_rate
from
    (
        select
            t1.uid,
            t1.dt,
            t2.first_time,
            case
                when t1.dt = t2.first_time then 1
                else 0
            end as uv_new
        from
            tb t1
            left join (
                select
                    uid,
                    min(date (in_time)) as first_time
                from
                    tb_user_log
                group by
                    uid
            ) t2 on t1.uid = t2.uid
    ) t3
group by
    t3.dt
order by
    t3.dt asc

--SQL SERVER

WITH
    tb as (
        select
            uid,
            CONVERT(nvarchar (10), in_time, 120) as dt
        from
            tb_user_log
        union
        select
            uid,
            CONVERT(nvarchar (10), out_time, 120) as dt
        from
            tb_user_log
    )
select
    t3.dt,
    count(DISTINCT t3.uid) as dau,
    round(sum(t3.uv_new) * 1.0 / count(DISTINCT t3.uid), 2) as uv_new_rate
from
    (
        select
            t1.uid,
            t1.dt,
            t2.first_time,
            case
                when t1.dt = t2.first_time then 1
                else 0
            end as uv_new
        from
            tb t1
            left join (
                select
                    uid,
                    min(CONVERT(nvarchar (10), in_time, 120)) as first_time
                from
                    tb_user_log
                group by
                    uid
            ) t2 on t1.uid = t2.uid
    ) t3
group by
    t3.dt
order by
    t3.dt asc