计算每月流失率-在SQL SERVER上

juud5qan 于 2022-12-03 发布在 SQL Server

关注(0)|答案(1)|浏览(239)

我正在尝试计算每月流失率（对于给定月份：退订人数/开始订阅人数
enter image description here
这样我就得到了一月份退订用户的单个百分比。但是，我想输出每个月的这个百分比，这样我就可以将其显示为折线图。我不确定从哪里开始-感觉像是我需要循环并运行每个月的查询，但感觉不对。我如何才能进行同样的计算呢？但是不需要手动指定月份，我们可以假设每个月至少有一个start_date和一个end_date，因此某种分组方式可能会起作用。

WITH
date_range AS (
SELECT '2022-10-01' AS start_date, '2022-10-31'AS end_date
),
start_accounts AS
(
SELECT DISTINCT ProductContractId
FROM HD s INNER JOIN date_range d ON
s.FirstInvoiceDate<= d.start_date
AND (s.ItemRejectDate>d.start_date or s.ItemRejectDate is null)
),
end_accounts AS
(
SELECT DISTINCT ProductContractId
FROM HD s INNER JOIN date_range d ON
s.FirstInvoiceDate<= d.end_date
AND (s.ItemRejectDate>d.end_date or s.ItemRejectDate is null)
),
churned_accounts AS
(
SELECT s.ProductContractId
FROM start_accounts s
LEFT OUTER JOIN end_accounts e ON
s.ProductContractId=e.ProductContractId
WHERE e.ProductContractId is null
),
start_count AS (
SELECT COUNT(*) AS n_start FROM start_accounts
),
churn_count AS (
SELECT COUNT(*) AS n_churn FROM churned_accounts
)
SELECT
convert(numeric(10,4),(n_churn* 1.0/n_start ))*100
AS churn_rate,
(1.0-(n_churn/n_start)*100)
AS retention_rate,
n_start,
n_churn
FROM start_count, churn_count

最后，我会寻找类似以下内容的输出：
enter image description here

sql-server

来源：https://stackoverflow.com/questions/74572320/calculating-monthly-churn-on-sql-server

1条答案

按热度按时间

5vf7fwbs1#

我还没有安装SQL Server但是这里有一个解决方案，它适用于《用数据打击客户流失》一书中的Postgres模式（https://www.fightchurnwithdata.com，这是问题中原始查询的基础。）关键是在第一个CTE中选择多个日期作为常量表（或使用为此目的定义的某个外部表），然后将其通过所有其他CTE，以便它一次计算多个客户流失率。每个churn计算期间的结束日期与日期间隔表达式一起使用（这肯定要为SQLServer更改，但我认为其余部分应该可以工作...）

WITH
calc_dates AS (
SELECT * from (values ('2020-02-01'::DATE),
    ('2020-03-01'::DATE),
    ('2020-04-01'::DATE)) as t (start_date)
),
start_accounts AS
(
    SELECT DISTINCT d.start_date, account_id
    FROM SUBSCRIPTION s INNER JOIN calc_dates d ON
    s.START_DATE<= d.start_date
    AND (s.END_DATE>d.start_date or s.END_DATE is null)
),
end_accounts AS
(
    SELECT DISTINCT d.start_date, account_id
    FROM SUBSCRIPTION s INNER JOIN calc_dates d ON
    s.START_DATE<=  (d.start_date+interval '1 month')
    AND (s.END_DATE>(d.start_date+interval '1 month') or s.end_date is null)
),
churned_accounts AS
(
    SELECT s.start_date, s.account_id
    FROM start_accounts s
    LEFT OUTER JOIN end_accounts e ON
    s.account_id=e.account_id
    and s.start_date = e.start_date
    WHERE e.account_id is null
),
start_count AS (
    SELECT start_date, COUNT(*)::FLOAT AS n_start 
    FROM start_accounts 
    group by start_date
),
churn_count AS (
    SELECT start_date, COUNT(*)::FLOAT AS n_churn 
    FROM churned_accounts 
    group by start_date
)
SELECT s.start_date,(s.start_date+interval '1 month')::date as end_date,
(n_churn* 1.0/n_start )*100
AS churn_rate
FROM start_count s
inner join churn_count c
on s.start_date=c.start_date
order by s.start_date;

在对book模式运行时，将提供以下输出：
| 开始日期（_D）|结束日期|客户流失率|
| - -|- -|- -|
| 2020年2月1日|2020年3月1日|6.42486011191047中国人民银行|
| 2020年3月1日|2020年4月1日|五、63271604938272|
| 2020年4月1日|2020年5月1日|4.83752524325317可再生能源|
我正在运行SQLServer，并将尝试更新解决方案很快...

赞(0）回复(0）举报 2022-12-03

我来回答

计算每月流失率-在SQL SERVER上

1条答案

相关问题

热门标签

最新问答