Churn Prediction

Meta’s growth team defines a "Churn Risk" user as someone whose total platform activity (posts, likes, comments) has dropped by 50% or more compared to the previous month.

Write a query to identify these users. A user should be flagged for every month where their activity was 50% or less of what it was in the immediately preceding month.

Rules:

• Consider activity across all types (post, like, comment).
• A user must have at least 1 action in the previous month to be evaluated for churn.
• The drop is calculated as: Current_Month_Activity / Previous_Month_Activity.
• If the result is 0.5 or less, the user is at risk.
• Output columns: user_id, report_month, activity_drop_ratio.
• Round activity_drop_ratio to 2 decimal places.
• Sort the result by user_id in ascending order, then report_month in ascending order.

Table Schema:

• User_Actions: action_id, user_id, action_type, action_date.
• Users: user_id, user_name, join_date.
• Action_Types: type_id, action_type, engagement_weight.