如何计算从其他商家转化客户产生的营收？SQL实现咨询

需求拆解 #

要计算的营收需满足三个核心条件：

• 客户先在商家1购买某款产品，之后在商家2（你的商家）购买同款产品
• 客户存在来自**其他商家（非1、非2）**的购买记录
• 仅统计商家2的相关订单营收总和

完善后的SQL语句 #

SELECT 
  SUM(cd.price) AS total_revenue,
  cd.customer_id,
  cd.product
FROM customerdata cd
-- 关联商家1的同款产品前置订单
INNER JOIN customerdata cd1
  ON cd.customer_id = cd1.customer_id
  AND cd.product = cd1.product
  AND cd1.business_id = 1
  AND cd.date_of_purchase > cd1.date_of_purchase
-- 确保客户有其他商家的购买记录
WHERE cd.business_id = 2
  AND EXISTS (
    SELECT 1
    FROM customerdata cd_other
    WHERE cd_other.customer_id = cd.customer_id
    AND cd_other.business_id NOT IN (1, 2)
  )
GROUP BY cd.customer_id, cd.product

语句说明 #

1. INNER JOIN 关联商家1订单：匹配同一客户、同款产品，且商家1的购买时间早于商家2的记录，确保"先买商家1再买商家2"的顺序
2. EXISTS子句筛选其他商家记录：验证客户存在除商家1、2外的其他购买行为
3. GROUP BY调整：原语句仅按business_id分组无法区分不同客户和产品，调整为按customer_id和product分组，更贴合需求的统计维度
4. 字段精简：去掉原SELECT中的date_of_purchase（聚合时该字段无意义），新增别名total_revenue明确统计结果

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