时间戳

首页 > 解决方案 > 查找不同订阅期的客户流失

sql-server - 查找不同订阅期的客户流失

问题描述

希望有人以前遇到过这个问题并有解决方案。我正在尝试根据订阅期而不是单个订单日期来查找失效的客户。我们将失效定义为在订阅结束后 30 天内未进行购买/续订。一个客户可以同时拥有多个订阅，并且订阅的长度可能会有所不同。我有一个数据集，其中包括客户 ID、订单、订阅开始日期、订阅到期日期以及该订单在客户订单历史记录中的排名，如下所示：

CREATE TABLE  #Subscriptions 
(CustomerID INT, 
Orderid INT, 
SubscriptionStart DATE, 
SubscriptionEnd DATE, 
OrderNumber INT);

INSERT INTO #Subscriptions
VALUES(1,   111111, '2017-01-01',   '2017-12-31',   1),
(1, 211111, '2018-01-01',   '2019-12-31'    ,2),
(1, 311121, '2018-10-01', '2018-10-02', 3),
(1, 451515, '2019-02-01',   '2019-02-28',   4),
(2, 158797, '2018-07-01',   '2018-07-31',   1),
(2, 287584, '2018-09-01',   '2018-12-31',   2),
(2, 387452, '2019-01-01',   '2019-01-31',   3),
(3, 187498, '2019-01-01',   '2019-02-28',   1),
(3, 284990, '2019-02-01',   '2019-02-28',   2),
(4, 184849, '2019-02-01',   '2019-02-28',   1)

在此数据集中，客户 2 将在 2018 年 7 月 31 日失效。由于客户 1 订阅了 2017-01-01 - 2017-12-31，然后订阅了从 2018-01-01 开始到 2019-12-31 结束的订阅，即使客户下达了其他订单，他们也不能在该时间段内失效将有资格。

我尝试使用 LEAD() 和 LAG() 进行一些简单的差距计算，但是，由于订阅期的长度可变，其中单个订阅可以跨越多个其他订单，因此我没有成功。最终，我们将使用它来计算大约 500 万条记录的月流失率。

标签： sql-servertsql

解决方案

您在尝试使用 LEAD() 和 LAG() 时想多了。您只需要 WHERE 子句中的 NOT EXISTS() 函数

在伪代码中：

SELECT...FROM...
WHERE {SubscriptionEnd is at least 30 days in the past}
AND NOT EXISTS(
  {A row for the same Customer where the StartDate is 30 days or less after this EndDate}
)

推荐阅读