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

  select a.dt,count(distinct a.uid) da_ac,
  round(sum(case when a.dt=b.mindt then 1 else 0 end)/count(distinct a.uid),2) perc 
  from 
  (select *
  from
  (select uid,date(in_time) dt from tb_user_log
  union all
  select uid,date(out_time) dt from tb_user_log)t1
  group by uid,dt)a left join 
  (select uid,min(dt) mindt
  from 
  (select *
  from
  (select uid,date(in_time) dt from tb_user_log
  union all
  select uid,date(out_time) dt from tb_user_log)t1
  group by uid,dt)a
  group by uid) b on a.uid=b.uid
  group by a.dt
  order by a.dt

用临时表思路更清晰

-- 每日活跃用户表
  create temporary table daliy_active_user
  (select *
  from
  (select uid,date(in_time) dt from tb_user_log
  union all
  select uid,date(out_time) dt from tb_user_log)t1
  group by uid,dt
  );
  -- 新用户表
  create temporary table new_user 
  (select uid,min(dt) mindt
  from daliy_active_user
  group by uid
  );
  -- 两表联结
  select a.dt,count(distinct a.uid) da_ac,
  round(sum(case when a.dt=b.mindt then 1 end)/count(distinct a.uid),2) perc 
  from daliy_active_user a left join new_user b on a.uid=b.uid
  group by a.dt
  order by a.dt;