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

# 每天活跃用户表
with po_table as (
select uid,date(in_time) as po_date
from tb_user_log
union
select uid,date(out_time) as po_date
from tb_user_log),
# 登陆信息表表
log_table as (
select uid,min(date(in_time)) as log_date
from tb_user_log
group by uid)

select po_date,count(distinct p.uid) as dau,
round(sum(if(l.uid is not null,1,0))/count(distinct p.uid),2) as uv_new_ratio
from po_table p
left join log_table l 
on p.uid = l.uid
and p.po_date = l.log_date
group by po_date