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

SELECT dt,dau,
IF(new_ids IS NULL,0.00,ROUND(new_ids/dau,2)) uv_new_ratio
FROM (
    SELECT dt,dau,new_ids
    FROM (
        SELECT DATE_FORMAT(ac_time,'%Y-%m-%d') dt,
        COUNT(DISTINCT uid) dau
        FROM(
            SELECT uid,DATE(in_time) ac_time
            FROM tb_user_log
            UNION
            SELECT uid,DATE(out_time) ac_time
            FROM tb_user_log
        )t1
        GROUP BY dt
    )t2
    LEFT JOIN (
        SELECT DISTINCT first_dt dt,
        COUNT(uid) new_ids
        FROM (
                SELECT uid,
                MIN(DATE(in_time)) AS first_dt FROM tb_user_log
                GROUP BY uid
            )t3
        GROUP BY first_dt
    )t4 USING(dt)
)t5
ORDER BY dt