如何正确计算用户留存率？SQL查询优化技术求助

问题背景 #

需要计算新注册用户在后续周期的留存情况，包括D1（次日留存）、D7（周留存）至D365（年留存）。核心要求是以T日的新注册用户数为分母计算对应留存率，但现有SQL的分母计算错误，无法得到正确结果。

表结构 #

期望查询结果 #

现有问题查询 #

原查询的错误在于分母使用count(*)，统计的是当日所有登录用户数，而非T日（installDate=目标日期）的新用户数：

SELECT
       loginDate AS Date,
       sum(CASE WHEN DATEDIFF(loginDate, installDate) = 1 THEN 1 END) / count(*) AS D1,
       sum(CASE WHEN DATEDIFF(loginDate, installDate) = 7 THEN 1 END) / count(*) AS D7
FROM logins
GROUP BY loginDate

有问题的CTE查询 #

基于他人思路修改的CTE查询仍未正确获取T日新用户数作为分母：

WITH cte1 AS
(SELECT loginDate,
       DATEDIFF(loginDate, installDate) Intervals,
       COUNT(loginDate=installDate) userCount
  FROM logins
GROUP BY Intervals, loginDate),
 cte2 AS (
  SELECT loginDate,
       SUM(CASE WHEN Intervals=0 THEN userCount ELSE 0 END) totalInstalledUser,
       SUM(CASE WHEN Intervals=1 THEN userCount ELSE 0 END) D1,
       SUM(CASE WHEN Intervals=7 THEN userCount ELSE 0 END) D7
FROM cte1
GROUP BY loginDate)
SELECT loginDate,
       (D1/totalInstalledUser)*100 D1Percentage,
       (D7/totalInstalledUser)*100 D7Percentage
   FROM cte2
   GROUP BY loginDate

修正后的SQL方案 #

核心思路：

1. 先统计每个installDate的新用户总数（即当日注册用户数）
2. 关联登录记录，计算每个loginDate对应的留存用户数
3. 用留存用户数除以对应installDate的新用户总数得到留存率

WITH daily_new_users AS (
    -- 统计每日新注册用户数
    SELECT 
        installDate,
        COUNT(DISTINCT userId) AS total_new_users
    FROM logins
    GROUP BY installDate
),
retention_users AS (
    -- 统计每个loginDate对应各留存周期的用户数
    SELECT 
        loginDate,
        -- 计算当前loginDate对应的installDate（即T日）
        DATEADD(day, -1, loginDate) AS d1_install_date,
        DATEADD(day, -7, loginDate) AS d7_install_date,
        COUNT(DISTINCT CASE WHEN DATEDIFF(day, installDate, loginDate) = 1 THEN userId END) AS d1_retention,
        COUNT(DISTINCT CASE WHEN DATEDIFF(day, installDate, loginDate) = 7 THEN userId END) AS d7_retention
    FROM logins
    GROUP BY loginDate
)
SELECT 
    ru.loginDate AS Date,
    -- 计算D1留存率，保留百分比格式，无数据则为NULL
    CASE 
        WHEN d1_retention IS NOT NULL AND dnu1.total_new_users > 0 
        THEN CONCAT(ROUND((d1_retention * 100.0 / dnu1.total_new_users), 0), '%')
        ELSE NULL 
    END AS D1,
    -- 计算D7留存率
    CASE 
        WHEN d7_retention IS NOT NULL AND dnu7.total_new_users > 0 
        THEN CONCAT(ROUND((d7_retention * 100.0 / dnu7.total_new_users), 0), '%')
        ELSE NULL 
    END AS D7
FROM retention_users ru
LEFT JOIN daily_new_users dnu1 ON ru.d1_install_date = dnu1.installDate
LEFT JOIN daily_new_users dnu7 ON ru.d7_install_date = dnu7.installDate
-- 只保留有留存数据的日期
WHERE d1_retention > 0 OR d7_retention > 0
ORDER BY ru.loginDate;

说明 #

• daily_new_users CTE：统计每日新增注册用户数，作为留存率的分母
• retention_users CTE：统计每个登录日中，符合D1、D7留存条件的用户数
• 主查询通过关联两个CTE，计算出对应的留存率，并格式化为百分比，无数据时显示NULL
• 使用COUNT(DISTINCT userId)避免同一用户多次登录被重复统计

内容的提问来源于stack exchange，提问作者curiousIT