题解 | #日活与每日次日留存率#

# 日活dau = 当日登入总用户数
# 每日次日留存率 = 连续登入用户数 / dau
-- 先写子查询，计算日活和次活
  SELECT
    u1.visit_date,
    COUNT(u1.id) AS dau ,
    COUNT(u2.id) AS next_dau
    FROM user_visit_log u1
    LEFT JOIN user_visit_log u2 ON u1.id = u2.id 
        AND DATEDIFF(u2.visit_date,u1.visit_date) = 1  --技巧,利用id和时间差为1进行匹配
    GROUP BY u1.visit_date
-- 这样就可以计算得到日活和次活；
然后从上述子查询提取日活，并求出次日留存率
完整代码：
SELECT 
    visit_date,
    dau,
   (next_dau/dau) AS next_day_per  -- 计算次日留存率
FROM (
    SELECT
    u1.visit_date,
    COUNT(u1.id) AS dau ,
    COUNT(u2.id) AS next_dau
    FROM user_visit_log u1
    LEFT JOIN user_visit_log u2 ON u1.id = u2.id 
        AND DATEDIFF(u2.visit_date,u1.visit_date) = 1
    GROUP BY u1.visit_date
) AS t1