SQL -新客户保留- MoM

Question

我正在努力找出我们每个月获得的新客户的保留期。已经从事务中识别了新客户逻辑，我对如何启动M1到M10没有线索

我需要如下所示，来解释一下表，在1月份我们获得了2500个客户，其中25000个新客户中只有1600个在M1(2月)交易，在1600个只有1200个在M2(Mar)中交易等等。

同样，在二月份，我们获得了2k客户，其中只有1100个在M1交易(M1在这里指Mar)，1100个只有800个在M2中交易(这里M2是指4月)。

M2是M1的子集，M3是M2的子集，等等。

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

​

​

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

Answer 2

我建议如下：

row_number()

• Aggregate.

• 按客户和月份汇总。
• 获取客户出现的最早月份，使用窗口函数。
• 获取最后一个没有下一个月的月份。

在SQL中，如下所示：

select year(first_yyyymm), month(first_yyyymm),
       count(*) 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, eomonth(date) as yyyymm,
             min(eomonth(date)) over (partition by customer) as first_eomonth,
             row_number() over (partition by customer order by eomonth(date)) as seqnum
      from transactions t
      group by customer, eomonth(date)
     ) t
where datediff(month, first_yyyymm, yyyymm) = seqnum - 1
group by year(first_yyyymm), month(first_yyyymm)
order by min(first_yyyymm);