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

WITH t0 AS (
	SELECT DISTINCT
		uid,
		date( in_time ) AS dt,
		min(
		date( in_time )) over ( PARTITION BY uid ) AS new_dt 
	FROM
		tb_user_log UNION
	SELECT DISTINCT
		uid,
		date( out_time ) AS dt,
		min(
		date( in_time )) over ( PARTITION BY uid ) AS new_dt 
	FROM
		tb_user_log 
	) SELECT
	dt,
	count(*) AS dau,
	round( sum( cnt )/ count(*), 2 ) AS uv_new_ratio 
FROM
	( SELECT *, IF ( dt = new_dt, 1, 0 ) AS cnt FROM t0 ORDER BY 2 ) t 
GROUP BY
	1