Calculate retention rate per date

Question

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

Codemia · Accepted Answer

The goal is to calculate the retention rate per date, grouped by region. The data involved likely includes user engagement records with the following key columns: `user_id`, `engagement_date`, and `region`. The retention rate is calculated as the number of users who engaged on a specific date divided by the number of users who engaged on the previous date, specifically for each region. This requires understanding both the unique users per date and the count of users who returned from the previous date.

1. **Identify Unique Users**: Use a CTE to select unique users and their first engagement date, ensuring to filter out duplicates.
2. **Calculate Daily Engagement**: Create another CTE to count the number of users engaged on each date, while grouping by region.
3. **Calculate Retention Rate**: Use a window function to compare the current day's engagement with the previous day's engagement, calculating the retention rate.
4. **Handle NULLs and Duplicates**: Utilize `COALESCE` to manage NULL values and ensure that duplicates are filtered out in the initial CTE.

```sql
WITH unique_users AS (
    SELECT DISTINCT user_id, MIN(engagement_date) AS first_engagement_date, region
    FROM user_engagements
    WHERE user_id IS NOT NULL
    GROUP BY user_id, region
),

daily_engagement AS (
    SELECT engagement_date, region, COUNT(DISTINCT user_id) AS user_count
    FROM user_engagements
    WHERE engagement_date IS NOT NULL
    GROUP BY engagement_date, region
)

SELECT de.engagement_date,
       de.region,
       de.user_count,
       COALESCE(LAG(de.user_count) OVER (PARTITION BY de.region ORDER BY de.engagement_date), 0) AS previous_user_count,
       CASE WHEN COALESCE(LAG(de.user_count) OVER (PARTITION BY de.region ORDER BY de.engagement_date), 0) = 0 THEN 0
            ELSE ROUND(CAST(de.user_count AS FLOAT) / COALESCE(LAG(de.user_count) OVER (PARTITION BY de.region ORDER BY de.engagement_date), 1), 2) END AS retention_rate
FROM daily_engagement de
ORDER BY de.engagement_date, de.region;
```

For large datasets, consider the following optimizations: 1. **Indexing**: Create indexes on `user_id`, `engagement_date`, and `region` to speed up filtering and grouping operations. 2. **Partitioning**: If the dataset is extremely large, partition the tables by date or region to improve query performance. 3. **Materialized Views**: If this retention calculation is performed frequently, consider using materialized views to store pre-aggregated results, thus reducing the computation time on subsequent queries.

1. **NULL Values**: Ensure to handle NULLs in `user_id` and `engagement_date` by filtering them out before aggregations. 2. **Duplicates**: The use of `DISTINCT` in the `unique_users` CTE addresses duplicates, but further checks may be necessary if there are other sources of duplication. 3. **No Engagement on Certain Dates**: If there are dates with no user engagement, the query should handle this gracefully, potentially returning a retention rate of 0 for those dates.

Calculate retention rate per date

Tesla

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

Tesla 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