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

1. 先求出每个用户按天统计的登录情况，注意in_time而out_time跨天的情况（union的用法）
2. 用窗口函数对用户登录进行排序，如果是首次登录则为1
3. 最外层再计算每日登录的用户数，按rn=1来筛选首次登录的用户

select dt,
count(distinct uid),
round(count(case when rn = 1 then 1 else null end) / count(distinct uid), 2)
from (
select uid,
dt,
row_number() over(partition by uid order by dt) rn
from 
(
select uid, date(in_time) dt from tb_user_log
union 
select uid, date(out_time) dt from tb_user_log 
    ) t1 ) t2
    group by 1 
    order by 1 asc