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天新增总用户数；

注意：留存一般是离散的概念，不要求用户在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 = 1 THEN user_id ELSE NULL END)/COUNT(user_id), 4)*100, '%') AS '次日留存率',
    CONCAT(ROUND(COUNT(CASE WHEN diff = 7 THEN user_id ELSE NULL END)/COUNT(user_id), 4)*100, '%') AS '7日留存率',
    CONCAT(ROUND(COUNT(CASE WHEN diff = 30 THEN user_id ELSE NULL END)/COUNT(user_id), 4)*100, '%') AS '30日留存率'
   FROM timediff
   GROUP BY DATE(first_login)
   ORDER BY date;