Join sessions and messages to find churn rate

Question

Write a query joining sessions and rides to produce the combined churn rate.

Codemia · Accepted Answer

To compute the churn rate for Doordash, we need to analyze two main data sources: the `sessions` table (which logs user sessions) and the `rides` table (which logs completed rides). The churn rate is typically defined as the percentage of users who stop using the service over a specific period. Our primary goal is to join these two datasets to identify users who had sessions but did not complete any rides, indicating potential churn.

1. **Identify Required Fields**: Start by determining the fields needed from both tables, such as user ID, session date from `sessions`, and ride dates from `rides`.
2. **Join Tables**: Use a LEFT JOIN to combine `sessions` and `rides` on the user ID, ensuring we retain all session records even if there are no corresponding rides.
3. **Filter and Group Data**: Filter the results to identify users who have had sessions but no rides. Then, group the data by session date to calculate the number of churned users.
4. **Calculate Churn Rate**: Finally, compute the churn rate by dividing the number of churned users by the total number of users who had sessions.

```sql
WITH UserSessions AS (
    SELECT user_id, COUNT(session_id) AS total_sessions
    FROM sessions
    GROUP BY user_id
),
RidesCompleted AS (
    SELECT user_id, COUNT(ride_id) AS total_rides
    FROM rides
    GROUP BY user_id
)
SELECT 
    (COALESCE(COUNT(DISTINCT U.user_id), 0) / NULLIF(COUNT(DISTINCT S.user_id), 0)) * 100 AS churn_rate
FROM UserSessions S
LEFT JOIN RidesCompleted U ON S.user_id = U.user_id
WHERE U.total_rides IS NULL;
```

To optimize this query for large datasets, consider the following strategies: 1. **Indexing**: Ensure that both `sessions` and `rides` tables have indexes on `user_id`. This will significantly speed up the JOIN operation. 2. **Partitioning**: If the datasets are very large, consider partitioning the tables by date or user ID to improve query performance. 3. **Materialized Views**: If this churn calculation is performed frequently, creating a materialized view that pre-aggregates the sessions and rides data can save computation time during query execution.

1. **NULL Values**: Handle NULLs appropriately when joining tables. The LEFT JOIN ensures that users with sessions but no rides are included. 2. **Duplicates**: Ensure that both `sessions` and `rides` tables do not contain duplicate entries for the same user on the same day. This could skew the churn rate calculation. 3. **Inactive Users**: Consider how to handle users who have never had a session. We may want to exclude them from the churn rate calculation or include them with a churn indicator.

Join sessions and messages to find churn rate

Doordash

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

Doordash 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