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

# 1.根据sql164题，得到新用户表t1：按用户分组，选取最小进入时间
# 2.得到用户活跃表t2：分别查询uid,in_time和uid,out_time，将结果合并union去重（跨天处理）
# 3.根据uid t1左连t2得到列名uid,min_in_time,dt的表
# 4.按dt分组，dau=去重uid，日活数=count(min_in_time=dt)/dau
SELECT dt,COUNT(DISTINCT t1.uid) dau,ROUND(SUM(IF(min_in_time = dt,1,0))/COUNT(DISTINCT t1.uid),2) uv_new_ratio
FROM
(SELECT uid,DATE(MIN(in_time)) min_in_time
FROM tb_user_log
GROUP BY uid) t1
LEFT JOIN
(SELECT uid,DATE(in_time) dt
FROM tb_user_log
UNION
SELECT uid,DATE(out_time) dt
FROM tb_user_log
) t2
ON t1.uid = t2.uid
GROUP BY dt
ORDER BY dt