题解 | #牛客每个人最近的登录日期(五)#
牛客每个人最近的登录日期(五)
http://www.nowcoder.com/practice/ea0c56cd700344b590182aad03cc61b8
每日留存率 = 每日新顾客中明日还消费的人数(tm表) / 每日新顾客数(td表)
两表都是基于每日新顾客这一信息,那首先要考虑建立所有顾客id+其首次消费日期作为基础表(new表),再推出tm与td表就很容易了。
需要注意的是,最终结果需要呈现所有日期,但是td与md表不含null的日期,所以考虑两表left join初始表login
Select distinct login.date as date,ifnull(tm.ca*1.0000/td.cA*1.00000,0.000)as P From login Left Join 每日新顾客数: (Select date,count(user_id) as cA From login WHERE (user_id,date)in (Select user_id,Min(date) From login Group By user_id)Group by date)as td on login.date=td.date Left Join 每日新顾客中明日还消费的人数: (Select new.date , count(distinct login.user_id) as ca From login Join (Select user_id as new_user,Min(date) as date From login Group By user_id)as new on login.user_id=new.new_user Where login.date=date((new.date),'+1 day') Group By new.date)as tm on td.date=tm.date Order By login.date