Write a query to find churn rate from sessions

Question

Write a SQL query to calculate churn rate from the sessions table, considering nulls and duplicates.

Codemia · Accepted Answer

To calculate the churn rate from the `sessions` table, we need to identify two key metrics: the number of active users during a specific period and the number of users who have churned (i.e., they have not returned after their last session). We will assume that a user is considered churned if they have not had a session in the last 30 days. The `sessions` table likely contains at least the following columns: `user_id`, `session_date`, and possibly other session-related data. The churn rate is defined as the number of churned users divided by the total number of users active at the beginning of the period.

1. **Identify Active Users**: First, we will determine the total number of unique users who had at least one session within the defined period (e.g., the last 30 days). 
2. **Identify Churned Users**: Next, we will find users who have not had any sessions in the last 30 days after their last session. 
3. **Calculate Churn Rate**: Finally, we will compute the churn rate using the formula: (Number of Churned Users / Total Active Users) * 100. 
4. **Handle NULLs and Duplicates**: We will ensure to handle NULL values in `user_id` and eliminate duplicates in sessions by using `DISTINCT`.

Here is the SQL query that calculates the churn rate:
```sql
WITH ActiveUsers AS (
    SELECT DISTINCT user_id
    FROM sessions
    WHERE session_date >= CURRENT_DATE - INTERVAL '30 days'
), 
ChurnedUsers AS (
    SELECT DISTINCT user_id
    FROM sessions
    WHERE user_id IS NOT NULL
    AND user_id NOT IN (
        SELECT DISTINCT user_id
        FROM sessions
        WHERE session_date >= CURRENT_DATE - INTERVAL '30 days'
    )
)
SELECT 
    (COUNT(DISTINCT cu.user_id) * 100.0 / NULLIF(COUNT(DISTINCT au.user_id), 0)) AS churn_rate
FROM 
    ActiveUsers au
LEFT JOIN 
    ChurnedUsers cu ON au.user_id = cu.user_id;
```

For large datasets, performance can be optimized by: 
1. **Indexing**: Ensure that there are indexes on `user_id` and `session_date` to speed up the filtering and joining processes. 
2. **Avoiding Subqueries**: Instead of using subqueries, consider using JOINs to reduce complexity and improve execution speed. 
3. **Partitioning**: If the `sessions` table is exceptionally large, partitioning it based on `session_date` could significantly enhance query performance.

Several edge cases to consider include: 
1. **NULL User IDs**: Ensure we filter out any NULL values for `user_id` to avoid counting them in churn or active users. 
2. **No Sessions**: If there are no sessions in the provided timeframe, the query should handle this gracefully by returning a churn rate of 0%. 
3. **All Users Churned**: If all users have churned, the denominator in the churn rate calculation will be zero; hence we use `NULLIF` to avoid division by zero errors.

Write a query to find churn rate from sessions

Neon

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

Neon 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