SQL⾯试题---计算⽤户留存率
给定user_behavior表，要求查询次⽇，7⽇和30⽇⽤户留存率。
`user_behavior`
+-------------------+---------+
| user_id          | int    |
| user_behavior_id  | int    |
| time              | datetime|
+-------------------+---------+
解题思路：1，⾸先clarify次⽇，7⽇和30⽇⽤户留存率的定义。现定为新⽤户第⼀次登录时间为第0天，新⽤户定义为第⼀次登录的⽤户，登录⾏为的代号为1。次⽇留存率：（第0天新增的⽤户中，新增⽇之后的第1天还登录的⽤户数）/第0天新增总⽤户数；7⽇留存率：（第0天新增的⽤户中，新增⽇之后的第7天还登录的⽤户数）/第0天新增总⽤户数；30⽇留存率：（第0天新增的⽤户中，新增⽇之后的第30天还登录的⽤户数）/第0天新增总⽤户数；
sql查询面试题及答案
注意：留存⼀般是离散的概念，不要求⽤户在N天内每天都登录
2，摘选出每天的新⽤户
3，列出每个新⽤户第⼀次登录的⽇期及此⽇期之后仍登录的⽇期
4，计算列出的登录⽇期之间的差值，如果相差1天，说明该新⽤户次⽇仍留存，如果相差7天，说明该新⽤户七⽇仍留存，以此类推
5，统计每天新⽤户的留存⼈数以及计算留存率
答案：
WITH new_user AS (
SELECT user_id, MIN(time) AS first_login
FROM user_behavior
WHERE user_behavior_id =1
GROUP BY user_id),
next_times AS (
SELECT new_user.user_id, new_user.first_login, user_behavior.time AS next_time
FROM new_user
LEFT JOIN user_behavior
ON new_user.user_id= user_behavior.user_id
AND user_behavior.time > new_user.first_login),
timediff AS (
SELECT user_id, first_login, DATEDIFF(first_login, next_time) AS diff
FROM next_times);
SELECT
DATE(first_login) AS date,
CONCAT(ROUND(COUNT(CASE WHEN diff =1THEN user_id ELSE NULL END)/COUNT(user_id), 4)*100, '%') AS'次⽇留存率',
CONCAT(ROUND(COUNT(CASE WHEN diff =7THEN user_id ELSE NULL END)/COUNT(user_id), 4)*100, '%') AS'7⽇留存率',
CONCAT(ROUND(COUNT(CASE WHEN diff =30THEN user_id ELSE NULL END)/COUNT(user_id), 4)*100, '%') AS'30⽇留存率'
FROM timediff
GROUP BY DATE(first_login)
ORDER BY date;

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。