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

with q0 as (select uid,in_time as time
from tb_user_log
union all
select uid,out_time as time
from tb_user_log),

q1 as (
SELECT 
uid,
date_format(TIME,'%Y-%m-%d') cur_day,
min(date_format(TIME,'%Y-%m-%d')) OVER(PARTITION BY uid) reg_time

FROM q0
),


q2 as (SELECT
cur_day,
count(DISTINCT uid) dau
FROM q1
GROUP BY cur_day),



q3 as (SELECT cur_day,count(DISTINCT uid) new_login
FROM q1
WHERE  reg_time=cur_day
GROUP BY cur_day)



SELECT q2.cur_day,dau,round((case when new_login is NULL THEN 0 ELSE new_login END)/dau,2) uv_new_ratio
FROM q2 LEFT JOIN q3 
ON q2.cur_day=q3.cur_day
ORDER BY 1

不知道为什么最近的这几题都写的很复杂，都是凑来凑去去凑出结果。。。。

得思考正确的路径是怎么编写的

原因：在最开始没有处理好数据，去重，以至于后来必须多写几个循环！！！

WITH t1 AS
    (SELECT DISTINCT uid,DATE(in_time) dt,MIN(DATE(in_time))OVER(PARTITION BY uid) new_dt
     FROM tb_user_log
     UNION SELECT DISTINCT uid,DATE(out_time) dt,MIN(DATE(in_time))OVER(PARTITION BY uid) new_dt
     FROM tb_user_log)
SELECT dt,
       COUNT(1) dau,
       ROUND(SUM(IF(dt=new_dt,1,0))/COUNT(1),2) uv_new_ratio
FROM t1
GROUP BY dt
ORDER BY dt ASC;