MySQL 60 天、90 天和每日流失率

Question

我一直致力于客户流失分析。我根据以下代码成功地估计了每月的流失率。这是我使用过的代码

with monthly_usage as (
  select 
    who_identifier, 
    datediff(month, '1970-01-01', when_timestamp) as time_period
  from events
  where event = 'login' group by 1,2 order by 1,2),

lag_lead as (
  select who_identifier, time_period,
    lag(time_period,1) over (partition by who_identifier order by who_identifier, time_period),
    lead(time_period,1) over (partition by who_identifier order by who_identifier, time_period)
  from monthly_usage),

lag_lead_with_diffs as (
  select who_identifier, time_period, lag, lead, 
    time_period-lag lag_size, 
    lead-time_period lead_size 
  from lag_lead),

calculated as (select time_period,
  case when lag is null then 'NEW'
     when lag_size = 1 then 'ACTIVE'
     when lag_size > 1 then 'RETURN'
  end as this_month_value,

  case when (lead_size > 1 OR lead_size IS NULL) then 'CHURN'
     else NULL
  end as next_month_churn,

  count(distinct who_identifier)
   from lag_lead_with_diffs
  group by 1,2,3)

select time_period, this_month_value, sum(count) 
  from calculated group by 1,2
union
select time_period+1, 'CHURN', count 
  from calculated where next_month_churn is not null
order by 1

但是，我有兴趣计算 60 天、90 天和每日流失率。有什么办法可以调整这个代码吗？我是 SQL 新手，目前正在研究 MySQL。我共享的代码已转换为 MySQL，输出如下所示。

Answer 1

您是否尝试过将 datediff 函数更改为“日”而不是月份？另外，你的 table 是由什么组成的？

这就是我计算每日流失率的方法:

CREATE TABLE tasks_used_da
            (
            date DATE,
            user_id BIGINT,
            account_id BIGINT,
            sum_tasks_used BIGINT
            )

WITH daily_churn as (
SELECT DISTINCT date_trunc('day', date) as day, user_id
FROM tasks_used_da
WHERE sum_tasks_used > 0
)
SELECT yesterday.day, COUNT(DISTINCT yesterday.user_id)
FROM daily_churn yesterday
LEFT JOIN daily_churn today
  ON today.user_id = yesterday.user_id
  AND today.day = yesterday.day + interval '28 days'
WHERE today.user_id is null
GROUP BY 1
ORDER BY 1;