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

WITH total AS(
SELECT 
uid, 
DATE(in_time) AS dt

FROM
tb_user_log 

UNION 

SELECT 
uid, 
DATE(out_time) AS dt

FROM
tb_user_log),

reg AS(
SELECT 
uid, 
DATE(MIN(in_time)) AS reg_date
FROM
tb_user_log
GROUP BY uid)

SELECT
dt,
COUNT(*) AS dau,
ROUND(COUNT(reg_date = dt OR NULL)/COUNT(*), 2) AS uv_new_ratio

FROM total
LEFT JOIN reg
    USING(uid)
    
GROUP BY dt

ORDER BY dt