Katkat
Back

Compute churn with re-subscriptions

OpenAIData Scientist · Technical Screen
HardData ScientistTechnical ScreenData Manipulation (SQL/Python)

Last updated

This problem tests your ability to reconstruct user subscription states over time from raw event logs, specifically identifying active status to calculate monthly churn rates.

Problem Statement

You are given a table subscriptions containing event logs for a SaaS platform. Each row records a user's subscription action. You need to calculate the churn rate for each month.

Schema

Table subscriptions (user_id, event_type, event_date)

  • event_type is either 'START' or 'CANCEL'.
  • A user is considered 'active' on any given day if their most recent event is 'START' or if they have ever started a subscription and have not explicitly cancelled it since their last start.
  • Churn rate for month M is defined as: (Users who were active in M-1 but not M) / (Total active users in M-1).

Task

Write a SQL query or Python function to calculate the monthly churn rate. Return the results for each month starting from the second month present in the dataset.

Examples

Input:

user_idevent_typeevent_date
1START2023-01-05
1CANCEL2023-02-10
2START2023-01-15

Output:

monthchurn_rate
2023-020.5

Explanation: In Jan, users 1 and 2 were active (Total: 2). In Feb, user 1 cancelled and user 2 stayed active. Only user 1 churned. Churn = 1/2 = 0.5.

Constraints

  • event_date is in 'YYYY-MM-DD' format.
  • Users can subscribe and cancel multiple times (re-subscriptions).
  • Assume at most 1 million rows.

Hints

Hint 1

First, create a daily status table or use a window function to determine the status of each user at the end of every month.

Hint 2

Use a 'Last Observation Carried Forward' (LOCF) approach to identify which users are active on the last day of each month.

Hint 3

Calculate the set of active users for each month, then perform a join or set difference to identify users present in the previous month but missing in the current one.

Follow-up questions

  • How would you modify your logic if a user could have multiple concurrent subscriptions?
  • What impact would adding a 'pause' subscription event type have on your data pipeline?
  • How would you optimize this query if the `subscriptions` table grew to billions of rows?