SQLite多查询结构开发：餐厅客户留存率周/日报表需求

一、按周/回溯周计算新客留存百分比 #

核心逻辑 #

• 同期首次下单客户：每周内完成首次下单（account_creation落在该周）的客户总数
• 复购留存客户：上述客户中，首次下单后14-21天内产生后续下单（last_ordr_date处于该时间窗口且不等于首单日期）的客户数
• 留存百分比公式：(复购客户数 ÷ 同期首次下单总客户数) × 100

优化后SQL查询 #

-- 按周统计留存率，输出"周数-年份"格式与留存百分比
WITH weekly_new_customers AS (
    -- 统计每周首次下单的客户总量
    SELECT
        strftime('%W-%Y', account_creation) AS week_year,
        COUNT(DISTINCT customer_id) AS total_new_customers
    FROM customer_tbl
    GROUP BY week_year
),
weekly_retained_customers AS (
    -- 统计每周首次下单且在14-21天内复购的客户数
    SELECT
        strftime('%W-%Y', account_creation) AS week_year,
        COUNT(DISTINCT customer_id) AS retained_customers
    FROM customer_tbl
    WHERE
        last_ordr_date >= date(account_creation, '+14 days')
        AND last_ordr_date <= date(account_creation, '+21 days')
        AND last_ordr_date != account_creation -- 排除首单，确保为复购行为
    GROUP BY week_year
)
SELECT
    wnc.week_year,
    ROUND((wr.retained_customers * 100.0 / wnc.total_new_customers), 2) AS retention_percentage
FROM weekly_new_customers wnc
LEFT JOIN weekly_retained_customers wr ON wnc.week_year = wr.week_year
ORDER BY wnc.week_year DESC;

优化亮点 #

• 用CTE拆分统计逻辑，提升代码可读性与维护性
• 采用strftime('%W-%Y', ...)标准化周格式（ISO周数+年份）
• 加入last_ordr_date != account_creation过滤首单，确保复购统计准确
• 左连接保留所有周数据，避免遗漏无复购的周期
• 用ROUND(...,2)控制百分比精度，适配业务报表需求

二、每日复购客户数与新增客户数比值报表 #

核心逻辑 #

• 每日新增客户：当日完成首次下单（account_creation为当日）的客户数
• 每日复购客户：当日完成复购的客户（last_ordr_date为当日且晚于首单日期），且其首次下单时间在当日的14-21天前
• 比值公式：每日复购客户数 ÷ 每日新增客户数

优化后SQL查询 #

-- 每日复购与新增客户数比值报表
WITH daily_new_customers AS (
    -- 统计每日新增客户数
    SELECT
        date(account_creation) AS report_date,
        COUNT(DISTINCT customer_id) AS daily_new_count
    FROM customer_tbl
    GROUP BY report_date
),
daily_retained_customers AS (
    -- 统计当日复购且首单在14-21天前的客户数
    SELECT
        date(last_ordr_date) AS report_date,
        COUNT(DISTINCT customer_id) AS daily_retained_count
    FROM customer_tbl
    WHERE
        last_ordr_date > account_creation -- 确认是复购行为
        AND date(account_creation) BETWEEN date(last_ordr_date, '-21 days') AND date(last_ordr_date, '-14 days')
    GROUP BY report_date
)
SELECT
    COALESCE(dnc.report_date, drc.report_date) AS report_date,
    COALESCE(dnc.daily_new_count, 0) AS daily_new_customers,
    COALESCE(drc.daily_retained_count, 0) AS daily_retained_customers,
    ROUND(COALESCE(drc.daily_retained_count * 1.0 / dnc.daily_new_count, 0), 2) AS retained_to_new_ratio
FROM daily_new_customers dnc
FULL JOIN daily_retained_customers drc ON dnc.report_date = drc.report_date
ORDER BY report_date DESC;

优化亮点 #

• 全连接覆盖所有有新增或复购的日期，避免数据缺失
• 用COALESCE处理NULL值，避免除法运算报错
• 精准匹配复购客户的首单时间窗口，贴合促销效果评估需求
• 保留原始统计数值，支持后续交叉分析

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