BigQuery中90-119天复购客户留存率计算错误排查

我在BigQuery中计算首次购买后90至119天内再次购买的客户留存率百分比，第一段SQL计算结果不正确，但第二段代码结果正确，不清楚第一段代码存在什么问题。

第一段（错误代码） #

with raw_data as (
select a.client_id
, a.date
, case when b.is_active = true then 1 else 0 end as retention
from table_prod_transactions a
left join table_prod_transactions b
on a.client_id = b.client_id
and b.date >= DATE_ADD(a.date, INTERVAL 90 DAY)
and b.date <= DATE_ADD(a.date, INTERVAL 119 DAY)
where a.date >= '2022-12-01'
and a.is_active = true
and date(a.first_time) = a.date
group by 1,2,3
)
select
date,
sum(retention)/count(distinct client_id)
from raw_data
group by 1
order by 1 asc

第二段（正确代码） #

with a as (
  SELECT distinct a.client_id client_first_purchase
    , a.date
FROM table_prod_transactions a
WHERE  a.date >= '2022-12-01'
  and date(a.first_time) = a.date
  and a.is_active = true
), a2 as (
  SELECT count(distinct a.client_id) client_first_purchase
    , a.date
FROM table_prod_transactions a
WHERE  a.date >= '2022-12-01'
  and date(a.first_time) = a.date
  and a.is_active = true
GROUP BY 2 
)
, d as (
  SELECT a.date
  ,count(distinct d.client_id) active_client
FROM table_prod_transactions d
JOIN a
  ON a.client_first_purchase = d.client_id
WHERE  d.date >= DATE_ADD(a.date, INTERVAL 90 DAY)
  and d.date <= DATE_ADD(a.date, INTERVAL 119 DAY)
  and date(d.first_time) = a.date
  and d.is_active = true
GROUP by 1
)
select a2.date
  , d.active_client/a2.client_first_purchase as m4
FROM 
d
JOIN a2
on  d.date = a2.date
GROUP BY 1,2
ORDER BY 1 ASC

第一段代码的问题分析 #

• 复购记录重复计数：左关联后，若客户在90-119天内有多次复购，会生成多条相同client_id的记录，每条记录的retention都会被标记为1。后续sum(retention)会累加这些重复的1，导致留存客户数被高估（比如客户有3次复购会被算成3个留存，实际应算1个）。
• 分组与聚合逻辑不匹配：外层查询用count(distinct client_id)得到了正确的首次购买客户基数，但分子sum(retention)是重复计算的结果，最终导致留存率数值偏高，不符合实际留存逻辑。

第二段代码则先提取首次购买客户列表，再分别统计首次购买总数和窗口期内的留存客户数（用count(distinct)确保每个客户只被计数一次），最终的除法逻辑完全符合留存率的定义，因此结果正确。

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