这是关于客户生命周期状态分析，它需要几件事：
1.客户获得日期（最好将其存储起来，因为有些客户可能会追溯到几年或几十年前）。对于此问题，我们假设revenue_analysis包含我们需要的所有内容，并计算user acquisition month

1. lapsed与churned的比较：一个churned的客户通常被定义为一段时间没有活动，对于这个问题，我们没有定义，因此，一个用户将永远被报告为lapsed。
   1.对于生命周期状态计算，我们将收集以下信息（member_id、calendar_month、acquisition_month、activity_month、prior_activity_month），以便计算最终结果。

with cte_new_user_monthly as (
select member_id,
       min(activity_year_month) as acquisition_month
  from revenue_analysis
 group by 1),
cte_user_monthly as (
select u.member_id,
       u.acquisition_month,
       m.yyyymm as calendar_month
  from cte_new_user_monthly u,
       calendar_month m
 where u.acquisition_month <= m.yyyymm),
cte_user_activity_monthly as (
select f.member_id,
       f.activity_year_month as activity_month
  from revenue_analysis f
 group by 1,2),
cte_user_lifecycle as (
select u.member_id,
       u.calendar_month,
       u.acquisition_month,
       m.activity_month
  from cte_user_monthly u
  left
  join cte_user_activity_monthly m
    on u.member_id = m.member_id
   and u.calendar_month = m.activity_month),
cte_user_status as (
select member_id,
       calendar_month,
       acquisition_month,
       activity_month,
       lag(activity_month,1) over (partition by member_id order by calendar_month) as prior_activity_month
  from cte_user_lifecycle),
user_status_monthly as (
select member_id,
       calendar_month,
       activity_month,
       case
          when calendar_month = acquisition_month then 'NEW'
          when prior_activity_month is not null and activity_month is not null then 'RETAINED'
          when prior_activity_month is not null and activity_month is null then 'UNRETAINED'
          when prior_activity_month is null and activity_month is not null then 'REACTIVATED'
          when prior_activity_month is null and activity_month is null then 'LAPSED'
          else null
       end as user_status
  from cte_user_status)
select member_id,
       calendar_month,
       activity_month,
       user_status,
       row_number() over (partition by member_id, user_status order by calendar_month) as months
  from user_status_monthly
 order by 1,2;

结果（包括activity_month以便于理解）：

member_id|calendar_month|activity_month|user_status|months|
---------+--------------+--------------+-----------+------+
     1001|        201701|        201701|NEW        |     1|
     1001|        201702|              |UNRETAINED |     1|
     1001|        201703|              |LAPSED     |     1|
     1001|        201704|              |LAPSED     |     2|
     1001|        201705|        201705|REACTIVATED|     1|
     1001|        201706|        201706|RETAINED   |     1|
     1001|        201707|              |UNRETAINED |     2|
     1001|        201708|              |LAPSED     |     3|
     1001|        201709|        201709|REACTIVATED|     2|
     1001|        201710|              |UNRETAINED |     3|
     1001|        201711|              |LAPSED     |     4|
     1001|        201712|        201712|REACTIVATED|     3|
     1002|        201703|        201703|NEW        |     1|
     1002|        201704|              |UNRETAINED |     1|
     1002|        201705|              |LAPSED     |     1|
     1002|        201706|              |LAPSED     |     2|
     1002|        201707|              |LAPSED     |     3|
     1002|        201708|              |LAPSED     |     4|
     1002|        201709|              |LAPSED     |     5|
     1002|        201710|              |LAPSED     |     6|
     1002|        201711|              |LAPSED     |     7|
     1002|        201712|              |LAPSED     |     8|

编辑：
1.在MySQL中测试的代码，因为我没有注意到'mysql'标记被删除。
1.代码中的calendar_month可以从calendar维度导出。