题解 | 计算用户的平均次日留存率

WITH t3 AS (
  SELECT 
    CASE
      WHEN t2.de2 IS NOT NULL THEN '2天活跃'  -- 修正原条件逻辑错误
      ELSE '非2天活跃'
    END AS ret,
    COUNT(*) AS number 
  FROM 
    (SELECT DISTINCT device_id de1, date d1 FROM question_practice_detail) t1
  LEFT JOIN
    (SELECT DISTINCT device_id de2, date d2 FROM question_practice_detail) t2
    ON t1.de1 = t2.de2 
    AND MONTH(t1.d1) = MONTH(t2.d2) 
    AND DAY(t1.d1) + 1 = DAY(t2.d2)
  GROUP BY ret
)

SELECT 
  (SELECT number FROM t3 WHERE ret = '2天活跃')  / 
  ((SELECT number FROM t3 WHERE ret = '2天活跃')+(SELECT number FROM t3 WHERE ret = '非2天活跃'))
  AS avg_ret