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

SELECT d.dt, d.dau, ROUND(IFNULL(c.new_dau,0)/d.dau,2) AS uv_new_ratio
FROM
        (SELECT b.dt, COUNT(DISTINCT b.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
                ) AS b
         GROUP BY b.dt 
        ) AS d

LEFT JOIN

        (SELECT a.dt, COUNT(DISTINCT a.uid) AS new_dau
        FROM
            (SELECT uid, MIN(DATE(in_time)) AS dt 
            FROM tb_user_log 
            GROUP BY uid
            ) AS a
        GROUP BY a.dt
        ) AS c
ON d.dt=c.dt
ORDER BY d.dt;