题解 | #计算每天的新用户占比#

#构造了三个临时表
with cte as (
select *,min(login_date)over(partition by uid) as min_date
from user_login_tb ),
cte1 as(
select min_date as dt,count(distinct uid) as new_num
from cte
group by dt
),
cte2 as (
select login_date as dt,count(distinct uid) as total_user_num
from user_login_tb
group by dt
)
select dt,total_user_num,
          concat(round(ifnull(new_num/total_user_num,0)*100,1),'%') as new_user_rate
from cte2 left join cte1 using(dt)
order by dt