牛客sql练习 - 71

描述

• 牛客每天有很多人登录，请你统计一下牛客每个日期新用户的次日留存率

drop table if exists login;
CREATE TABLE `login` (
`id` int(4) NOT NULL,
`user_id` int(4) NOT NULL,
`client_id` int(4) NOT NULL,
`date` date NOT NULL,
PRIMARY KEY (`id`));

INSERT INTO login VALUES
(1,2,1,'2020-10-12'),
(2,3,2,'2020-10-12'),
(3,1,2,'2020-10-12'),
(4,2,2,'2020-10-13'),
(5,1,2,'2020-10-13'),
(6,3,1,'2020-10-14'),
(7,4,1,'2020-10-14'),
(8,4,1,'2020-10-15');

考点

• 不同开窗函数的用法
• ROW_NUMBER
• LEAD(variable,N)

SELECT a.date,IFNULL(ROUND(liucun/newone,3),0)
FROM
(SELECT *,
SUM(CASE WHEN rm = 1 THEN 1 ELSE 0 END) liucun
SUM(CASE WHEN DATEDIFF(nextdate,date) = 1 THEN 1 ELSE 0 END) newone
FROM
(SELECT *,
ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY date) rm,
LEAD(date,1) OVER(PARTITION BY user_id ORDER BY date) nextdate
FROM login
) a
GROUP BY a.date) b