题解 | #2021年11月每天新用户的次日留存率#

 -- mySQL

select
    first_in_time,
    round(count(t2.uid) / count(t1.uid), 2) as uv_rate
from
    (
        SELECT
            uid,
            min(date (in_time)) as first_in_time
        FROM
            tb_user_log
        GROUP BY
            uid
    ) t1
    left join (
        SELECT
            uid,
            date (in_time) as dt
        FROM
            tb_user_log
        union
        SELECT
            uid,
            date (out_time) as dt
        FROM
            tb_user_log
    ) t2 on t1.uid = t2.uid
    and t1.first_in_time = date_sub(t2.dt, interval 1 day)
where
    date_format (t1.first_in_time, '%Y-%m') = '2021-11'
group by
    first_in_time
ORDER BY
    first_in_time

--SQL SERVER

select
    t1.first_in_time,
    ROUND(COUNT(t2.dt) * 1.0 / count(t1.first_in_time), 2) AS uv_rate_1day
from
    (
        SELECT
            uid,
            MIN(CONVERT(nvarchar (10), in_time, 120)) as first_in_time
        FROM
            tb_user_log
        GROUP BY
            uid
    ) t1
    left join (
        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
    ) t2 on t1.uid = t2.uid
    and t1.first_in_time = DATEADD (day, -1, t2.dt)
where
    CONVERT(nvarchar (7), t1.first_in_time, 120) = '2021-11'
GROUP BY
    t1.first_in_time
ORDER BY
    t1.first_in_time