SQL 面试系列（一）【留存率问题】

-- 留存率问题
-- 示例表
DROP TABLE IF EXISTS t_user;
CREATE TABLE t_user(
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_name VARCHAR(50) NOT NULL,
  register_time DATETIME NOT NULL
);
 
SET SESSION cte_max_recursion_depth=9999999;
 
INSERT INTO t_user(user_name, register_time)
WITH RECURSIVE t AS (
  SELECT 1 n, '2022-01-01 00:00:00' d
  UNION ALL
  SELECT n+1, d + INTERVAL '1' MINUTE
  FROM t
  WHERE n<10000
)
SELECT concat('user', n), d FROM t;
 
SELECT date(register_time), count(*) FROM t_user GROUP BY date(register_time);
 
DROP TABLE IF EXISTS t_user_login;
CREATE TABLE t_user_login(
  id INT AUTO_INCREMENT PRIMARY KEY,
  uid INT NOT NULL,
  login_time DATETIME NOT NULL
);
 
SET SESSION cte_max_recursion_depth=9999999;
 
SELECT @@cte_max_recursion_depth;
 
INSERT INTO t_user_login(uid, login_time)
WITH RECURSIVE t AS (
  SELECT 1 n, rand()*10000 id, '2022-01-01 00:00:00' d
  UNION ALL
  SELECT n+1, rand()*10000, d + INTERVAL CEIL(n/3000) second
  FROM t
  WHERE n<500000
)
SELECT CEIL(id), d FROM t;

SELECT COUNT(DISTINCT tul.uid)/COUNT(DISTINCT tu.id) rr1
FROM t_user tu
LEFT JOIN t_user_login tul ON (tul.uid = tu.id AND DATE(tul.login_time) = DATE(tu.register_time) + INTERVAL '1' DAY)
WHERE tu.register_time BETWEEN '2022-01-01 00:00:00' AND '2022-01-01 23:59:59';

SELECT date(u.register_time),
       100*count(DISTINCT ul1.uid)/count(DISTINCT u.id) rr1,
       100*count(DISTINCT ul2.uid)/count(DISTINCT u.id) rr3,
       100*count(DISTINCT ul3.uid)/count(DISTINCT u.id) rr7,
       100*count(DISTINCT ul4.uid)/count(DISTINCT u.id) rr30
FROM t_user u
LEFT JOIN t_user_login ul1 ON (ul1.uid = u.id AND date(ul1.login_time) = date(u.register_time) + INTERVAL '1' DAY)
LEFT JOIN t_user_login ul2 ON (ul2.uid = u.id AND date(ul2.login_time) = date(u.register_time) + INTERVAL '3' DAY)
LEFT JOIN t_user_login ul3 ON (ul3.uid = u.id AND date(ul3.login_time) = date(u.register_time) + INTERVAL '7' DAY)
LEFT JOIN t_user_login ul4 ON (ul4.uid = u.id AND date(ul4.login_time) = date(u.register_time) + INTERVAL '30' DAY)
GROUP BY date(u.register_time);

WITH t1 AS(
SELECT tu.id,tul.uid,DATE(tu.register_time) reg_date,DATE(tul.login_time) login_date,
       DENSE_RANK() OVER (PARTITION BY DATE(tu.register_time) ORDER BY tu.id) reg_rk, -- 按照用户注册日期进行分区 并按照用户id进行排名 得到的max(排名)就是当天注册的总人数
       DENSE_RANK() OVER (PARTITION BY DATE(tu.register_time),DATE(tul.login_time) ORDER BY tul.uid) log_rk -- max(排名)就是当天登录的人数
FROM t_user tu
LEFT JOIN t_user_login tul
ON (tu.id = tul.uid
    AND(DATE(tul.login_time) = DATE(tu.register_time) + INTERVAL 1 DAY
        OR DATE(tul.login_time) = DATE(tu.register_time) + INTERVAL 3 DAY
        OR DATE(tul.login_time) = DATE(tu.register_time) + INTERVAL 7 DAY
        OR DATE(tul.login_time) = DATE(tu.register_time) + INTERVAL 30 DAY ))
),
t2 AS (
    -- 这里的 MAX(reg_rk) 并不代表 reg_date 这一天注册的人数，因为它是按照 reg_date 和 login_date 共同分组的
    -- 所以最细粒度是 log_date 而不是 reg_date，所以当只按照 reg_date 分组时得到的才是
    -- 所以这里得到的结果中 log_sum 可以代表reg_date注册后的在log_date登录的用户数，而 reg_sum 并不能代表reg_date注册的总人数
    SELECT reg_date, login_date, MAX(reg_rk) reg_sum, MAX(log_rk) log_sum
    FROM t1
    GROUP BY reg_date,login_date -- 因为是 left join 所以会保留所有注册信息，这里同时按照 login_date 分组就会把 login_date为null的数据排除在统计范围之外
)
SELECT reg_date, MAX(reg_sum), -- 这里的max的粒度才是reg_date,得到才是正确的注册人数
       MAX(CASE WHEN login_date = reg_date + INTERVAL 1 DAY THEN log_sum END)/MAX(reg_sum) rr1,
       MAX(CASE WHEN login_date = reg_date + INTERVAL 3 DAY THEN log_sum END)/MAX(reg_sum) rr3,
       MAX(CASE WHEN login_date = reg_date + INTERVAL 7 DAY THEN log_sum END)/MAX(reg_sum) rr7,
       MAX(CASE WHEN login_date = reg_date + INTERVAL 30 DAY THEN log_sum END)/MAX(reg_sum) rr30
from t2
GROUP BY reg_date;