What we have:

 user_id month
    ---- 2021-08  
    1414 2021-09  
    1414 2021-10 
    1414 2021-11
    ---- 2021-12

What we need:

 user_id month
    ---- 2021-08
    1414 2021-09 new user
    1414 2021-10 old
    1414 2021-11 churn
    ---- 2021-12

in the end, I'll aggregate all of the users with COUNT(new_user) and GROUP BY status...

However, I have a problem with this stage where I need to assign correct values to users within certain months

CodePudding user response：

Something like this might work.

The first test CTE term is just to provide the test table data.

WITH test (user_id, month) AS (
    SELECT 1414, '2021-09' UNION
    SELECT 1414, '2021-10' UNION
    SELECT 1414, '2021-11' UNION
    SELECT null, '2021-08' UNION
    SELECT null, '2021-12'
     )
   , xrows AS (
    SELECT *
         , LAG(month)  OVER (PARTITION BY user_id ORDER BY month) AS lastval
         , LEAD(month) OVER (PARTITION BY user_id ORDER BY month) AS nextval
      FROM test
     )
SELECT user_id, month
     , CASE WHEN user_id IS NOT NULL THEN
            CASE WHEN nextval IS NULL THEN 'churn'
                 WHEN lastval IS NULL THEN 'new user'
                 ELSE 'old'
             END
        END AS status
  FROM xrows
 ORDER BY month
;

-- or

WITH test (user_id, month) AS (
    SELECT 1414, '2021-09' UNION
    SELECT 1414, '2021-10' UNION
    SELECT 1414, '2021-11' UNION
    SELECT null, '2021-08' UNION
    SELECT null, '2021-12'
     )
   , xrows AS (
    SELECT *
         , LAG(month)  OVER w AS lastval
         , LEAD(month) OVER w AS nextval
      FROM test
    WINDOW w AS (PARTITION BY user_id ORDER BY month)
     )
SELECT user_id, month
     , CASE WHEN user_id IS NOT NULL THEN
            CASE WHEN nextval IS NULL THEN 'churn'
                 WHEN lastval IS NULL THEN 'new user'
                 ELSE 'old'
             END
        END AS status
  FROM xrows
 ORDER BY month
;

Result: