Calculate churn rate per region

Question

Write a query to compute churn rate grouped by region, handling edge cases like nulls and duplicates.

Codemia · Accepted Answer

To calculate the churn rate per region for Slack, we need to analyze user data that includes information about user subscriptions and their statuses over time. The churn rate is defined as the percentage of users who stop using the service within a given period. Specifically, we will need:

1. A table containing user subscription records, including fields like `user_id`, `subscription_start_date`, `subscription_end_date`, `status` (active or churned), and `region`.
2. We will compute the churn rate by grouping this data by `region` and calculating the number of churned users against the total number of users who had an active subscription during the considered time frame.

To build the SQL query, we will follow these steps:
1. **Identify Active and Churned Users**: Use a CTE to filter out users based on their subscription status and define what constitutes an active user and a churned user.
2. **Calculate Total Users and Churned Users**: In the same CTE or a subsequent one, count the total number of active users and the number of churned users for each region.
3. **Compute Churn Rate**: After obtaining the counts, calculate the churn rate as the ratio of churned users to total users.
4. **Handle Edge Cases**: Consider null values in the region or status fields and ensure these do not affect the computations.

Here is the SQL query that implements the above approach:
```sql
WITH UserStatus AS (
    SELECT
        user_id,
        region,
        status,
        CASE 
            WHEN status = 'churned' THEN 1 
            ELSE 0 
        END AS is_churned
    FROM users
    WHERE region IS NOT NULL AND status IS NOT NULL
),
UserCounts AS (
    SELECT
        region,
        COUNT(user_id) AS total_users,
        SUM(is_churned) AS churned_users
    FROM UserStatus
    GROUP BY region
)
SELECT
    region,
    total_users,
    churned_users,
    CASE
        WHEN total_users = 0 THEN 0.0
        ELSE churned_users::float / total_users
    END AS churn_rate
FROM UserCounts;
```

To optimize this query for large datasets:
1. **Indexing**: Create indexes on the `region` and `status` columns to speed up filtering and grouping operations. This can significantly reduce the execution time by allowing the database to quickly locate relevant rows.
2. **Partitioning**: If the dataset is extremely large, consider partitioning the user data by region to improve the performance of aggregation operations.
3. **Materialized Views**: If churn calculations are frequently required, consider creating a materialized view that pre-aggregates churn data by region, thereby reducing the need to compute the same values repeatedly.

When dealing with this query, we must consider and handle the following edge cases:
1. **NULL Values**: Users with NULL in the `region` or `status` should be excluded from the analysis as they can skew the results.
2. **Duplicates**: Ensure the `user_id` is unique in the dataset; if duplicates exist, they may inflate the total user count. Use DISTINCT when counting users if necessary.
3. **No Churned Users**: Handle scenarios where there are no churned users in a region, which could lead to a division by zero when calculating the churn rate. Use a CASE statement to return a churn rate of 0.0 in such cases.

Calculate churn rate per region

Slack

What the Interviewer Expects

Key Topics to Cover

How to Approach This

Possible Follow-up Questions

Sharpen Your Skills on Codemia

Sample Answer

Problem Understanding

Approach

Submit Your Answer

Slack Data Scientist Interview Guide

Related Questions

Optimize a slow query on products

Calculate conversion rate per user from orders

Window function: rank over sessions

Optimize a slow query on orders

Write a query to find time to first action from sessions