牛客每个人最近的登录日期(五)题解
牛客每个人最近的登录日期(五)
http://www.nowcoder.com/questionTerminal/ea0c56cd700344b590182aad03cc61b8
这个题目如果你做过牛客每个人最近的登录日期(三)和牛客每个人最近的登录日期(四)，那么解决起来会容易很多，做过(四)https://blog.nowcoder.net/n/a2f955514f824bb888f9d7726421e809我们知道每个日期的新用户求法为:
select login.date,ifnull(n1.new_num,0) from login left join (select l1.date,count(distinct l1.user_id) as new_num from login l1 where l1.date = (select min(date) from login where user_id=l1.user_id) group by l1.date) n1 on login.date = n1.date group by login.date做过(三) https://blog.nowcoder.net/n/1dad24440b3e45949dfda03cb1f06a9e的话，我们可以轻松得到每个日期新用户次日还登录的人的个数的sql语句如下:
select l1.date,count(distinct l1.user_id) from login l1 join login l2 on l1.user_id=l2.user_id and l2.date=date((l1.date),'+1 day') where l1.date = (select min(date) from login where user_id=l1.user_id) group by l1.date;得到的结果如下:
2020-10-12|2 2020-10-14|1又在(四)里面提到过联立主表，将查询次日还登录的人的sql也联立主表：
(select login.date,ifnull(n1.new_num,0) as second_login_num from login left join (select l1.date,count(distinct l1.user_id) as new_num from login l1 join login l2 on l1.user_id=l2.user_id and l2.date=date((l1.date),'+1 day') where l1.date = (select min(date) from login where user_id=l1.user_id) group by l1.date) n1 on login.date = n1.date group by login.date) second_login就可以得到:
2020-10-12|2 2020-10-13|0 2020-10-14|1 2020-10-15|0的结果了，然后将这2个表联立相除，得到的结果保留3为，用ifnull判断0/0的情况，那么就可以得到完整结果了：
select second_login.date, round(ifnull(second_login.second_login_num *1.0/ first_login.first_num,0),3) from (select login.date,ifnull(n1.new_num,0) as second_login_num from login left join (select l1.date,count(distinct l1.user_id) as new_num from login l1 join login l2 on l1.user_id=l2.user_id and l2.date=date((l1.date),'+1 day') where l1.date = (select min(date) from login where user_id=l1.user_id) group by l1.date) n1 on login.date = n1.date group by login.date) second_login join (select login.date,ifnull(n1.new_num,0) as first_num from login left join (select l1.date,count(distinct l1.user_id) as new_num from login l1 where l1.date = (select min(date) from login where user_id=l1.user_id) group by l1.date) n1 on login.date = n1.date group by login.date) first_login on second_login.date=first_login.date
查看9道真题和解析
投递阿里云等公司10个岗位