sql - SQL - 新客户保留 - MoM

Question

我试图确定我们每个月获得的新客户的保留期。已经从交易中识别出新客户逻辑，我对如何启动 M1 到 M10 没有任何指导

我需要得到类似下面的东西来解释表格，在 1 月份我们已经获得了 2500 个客户，在这 2500 个新客户中，在 M1（2 月）仅交易了 1600 个，在这 1600 个新客户中，在 M2（3 月）仅交易了 1200 个等等

同样的，在2月份我们获得了2000个客户，其中只有1100个在M1交易（这里M1是指3月），在这1100个客户中只有800个在M2交易（这里M2是指4月）

M2 是 M1 的子集，M3 是 M2 的子集，依此类推。

使用 SQL Server 2012，由于对我的角色和访问权限的某些限制，我想避免对数据进行预处理。任何带有 sql 逻辑的线索都会有所帮助。

标签： sqlsql-serversql-server-2012

Answer 1

根据戈登的回答，我提出了解决方案：http ://sqlfiddle.com/#!18/f6785/3

select
  year(first_yyyymm),
  month(first_yyyymm),
  count(distinct customer_id) as new_customers,
  sum(case when seqnum = 1 then 1 else 0 end) as m1,
  sum(case when seqnum = 2 then 1 else 0 end) as m2,
  sum(case when seqnum = 3 then 1 else 0 end) as m3,
  sum(case when seqnum = 4 then 1 else 0 end) as m4,
  sum(case when seqnum = 5 then 1 else 0 end) as m5,
  sum(case when seqnum = 6 then 1 else 0 end) as m6,
  sum(case when seqnum = 7 then 1 else 0 end) as m7,
  sum(case when seqnum = 8 then 1 else 0 end) as m8,
  sum(case when seqnum = 9 then 1 else 0 end) as m9,
  sum(case when seqnum = 10 then 1 else 0 end) as m10
from
  (
    select
      customer_id,
      first_yyyymm, yyyymm,
      datediff(month, first_yyyymm, yyyymm) as seqnum
    from
      (
        select
          customer_id,
          eomonth(created_at) as yyyymm,
          min(eomonth(created_at))
            over (partition by customer_id) as first_yyyymm
        from transactions t
        group by customer_id, eomonth(created_at)
      ) t
  ) t
group by year(first_yyyymm), month(first_yyyymm)
order by month(first_yyyymm);

对于数据：

结果应为：

编辑

这是另一种解决方案，仅计算每月有交易的客户。

http://sqlfiddle.com/#!18/ad3803/2