SQLite月度客户留存计算问题求解

数据表结构 #

留存规则 #

客户ID同时出现在当前月和上月，即视为留存客户：

• abc和xyz同时出现在2022-05和2022-06，因此2022-06的留存客户数为2
• asd在2022-05出现后，2022-06未出现，2022-07再次出现，不计入留存

实现SQL语句 #

SELECT
    curr.year_month,
    COUNT(DISTINCT curr.customer_id) AS customers_retained
FROM
    your_table_name curr
JOIN
    your_table_name prev ON curr.customer_id = prev.customer_id
    AND strftime('%Y%m', curr.year_month || '-01') = strftime('%Y%m', date(prev.year_month || '-01', '+1 month'))
GROUP BY
    curr.year_month
HAVING
    COUNT(DISTINCT prev.customer_id) > 0;

语句说明 #

• 自连接关联：将表自身拆分为当前月（curr）和上月（prev）两个数据集，通过customer_id匹配同一客户
• 日期逻辑处理：通过date函数将上月的year_month转为日期后加1个月，再用strftime统一格式为YYYYMM，确保与当前月严格匹配
• 去重计数：使用COUNT(DISTINCT)避免同一客户在当月多次出现导致的重复统计
• 过滤无效月份：HAVING子句过滤掉没有上月数据的月份（如2022-05）

预期输出 #

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