WITH
    first_login AS (
        SELECT
            uid,
            MIN(DATE(in_time)) AS first_login_time
        FROM
            tb_user_log
        GROUP BY
            uid
    ),
    daily_active_user AS (
        SELECT
            t1.dt,
            COUNT(t1.uid) AS dau
        FROM
            (
                SELECT
                    uid,
                    DATE(in_time) AS dt
                FROM
                    tb_user_log
                UNION
                SELECT
                    uid,
                    DATE(out_time) AS dt
                FROM
                    tb_user_log
            ) t1
        GROUP BY
            t1.dt
    )
SELECT
    d.dt,
    d.dau,
    ROUND(COUNT(f.uid) * 1.0 / d.dau, 2) AS uv_new_ratio
FROM daily_active_user d
LEFT JOIN first_login f
    ON f.first_login_time = d.dt
GROUP BY
    d.dt,
    d.dau
ORDER BY
    d.dt;