和之前一样的逻辑，理顺了之后越做越快，6行代码搞定。

一、题目理解

  • 统计每天的日活数及新用户占比
  • 新用户占比=当天的新用户数÷当天活跃用户数（日活数）。
  • 如果in_time-进入时间和out_time-离开时间跨天了，在两天里都记为该用户活跃过。
  • 新用户占比保留2位小数，结果按日期升序排序。
这几句话给的核心信息是，in_time和out_time都算作是活跃日，同时要把用户首次登录的日期找出来，最后再计算新用户占比。

二、解题步骤

1）老方法，先建立一张拥有基本信息的用户活跃基础表
  • 这张表要包含用户id，活跃日，成为新用户的日期。因为用户可能1天活跃N次，所以要做去重处理。
  • 活跃日直接并联in_time和out_time
  • 成为新用户日期，用窗口函数来取：MIN(DATE(in_time))OVER(PARTITION BY uid)  AS new_dt
代码如下： 
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
2）定义新用户
如果dt=new_dt那这天就是用户首次登录成为新用户的日子啦~ 
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 uid,dt,IF(dt=new_dt,1,0) '是否为新用户（是为1，不是为0）'
FROM t1;

3）计算新用户占比，结果按照日期升序，输出结果。
  • 日活：COUNT(1)
  • 新用户数：SUM（是否为新用户）
  • 新用户占比：ROUND(SUM(新用户)/COUNT(1),2) 
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;
完成啦~是不是很好理解~