SQL269热题难！

查询每个日期新用户的次日留存率，1个表是新用户第一次登录min(date)，第2个表是同一个用户在第二天登录

代码如下：

select l.date, ifnull(round(count(distinct l2.user_id)/count(l1.user_id),3),0)p from login l left join login l1 on l1.id = l.id

and (l1.user_id, l1.date) in

(

    select user_id, min(date)over(partition by user_id) from login

)

left join login l2 on l1.user_id = l2.user_id and l1.date = l2.date - 1

group by l.date

order by l.date ASC