题解 | #查找在职员工自入职以来的薪水涨幅情况#

建立两个临时表，分别计算每一天的新增用户和第二天仍旧进入的用户数目怒

#第一步：计算每天的新增用户
with t1 as 
(
select date,count(first_date) as new
from login l 
left join 
(
select user_id,min(date) as first_date from login group by user_id 
)t 
on l.user_id=t.user_id
and l.date=t.first_date
group by date
),
#第二步：计算下一天的留存用户数目
#就是第一天登录，第二天也登录的用户
t2 as 
(
select date,count(*) as liucun
from login 
where (user_id,date) 
in (select user_id,date_add(min(date),interval 1 day) from login group by user_id)
group by date
) 

select t1.date,ifnull(round(if(t1.new=0,0,t2.liucun/t1.new),3),0) as p
from t1
left join t2
on date_add(t1.date,interval 1 day)=t2.date