SQL BigQuery. Check if user bought something previous month = old, if he didn’t buy anything next month = churn

SQL BigQuery. Check if user bought something previous month = old, if he didn’t buy anything next month = churn

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