题解 | #剔除最后一个日期的新用户(结合窗口函数）#

剔除最后一个日期的新用户

考虑到可能最后一个日期有新用户，因此这些新用户无法计算留存率，需要剔除，因此建立临时表temp。语句如下：

with temp as
(select
   t1.user_id,
   t1.client_id,
   t1.date
from
    (select
      user_id,
       client_id,
      date,
       min(date) over(partition by user_id) userStartDate,
       max(date) over() currentDate
    from
       login
   ) t1
where
   t1.userStartDate<t1.currentDate
)
select
    round(count(distinct user_id)*1.0/(select count(distinct user_id) from temp),3) p
from
    temp
where
   (user_id,date) in (select user_id,date_add(date,interval 1 day) from temp);