Hive SQL必刷练习题：留存率问题（*****）-代码：

select
  t3.first_login,
  t3.register,
  t3.remain_1/t3.register retention
from
  (
   select
     t1.first_login,
     count(t1.user_id) register,
     count(t2.user_id) remain_1
   from
     (
      select
        user_id,
        date_format(min(login_ts),'yyyy-MM-dd')   first_login
      from
        user_login_detail
      group by
        user_id
      )t1
   left join
     user_login_detail t2
   on
     t1.user_id=t2.user_id and datediff(date_format(t2.login_ts,'yyyy-MM-dd'),t1.first_login)=1
   group by
     t1.first_login
)t3