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

select c.dt, c.dau, round(ifnull(b.nu/c.dau,0),2)
from
(select a.dt,count(distinct a.uid) nu
from
(select uid, date_format(min(in_time), '%Y-%m-%d') dt
from tb_user_log
group by uid) a
group by a.dt) b
right join
(select a.dt, count(distinct a.uid) dau 
from
(select date_format(in_time, '%Y-%m-%d') dt, uid
from tb_user_log
union all
select date_format(out_time, '%Y-%m-%d') dt, uid
from tb_user_log) a
group by a.dt) c
on b.dt=c.dt
order by c.dt

• 找到每个日期对应的新用户数量
• 找到in_time out_time各自的活跃用户
• 用union all 得到结果
• 使用count 和distinct计数
• 用ifnull保证输出非none
• 用round保留两位小数