Join sessions and clicks to find retention rate

Question

Write a query joining sessions and users to produce the combined retention rate.

Codemia · Accepted Answer

To calculate the retention rate, we need to join two main datasets: **sessions** and **users**. The **sessions** table contains records of user sessions, including timestamps and user IDs, while the **users** table contains details about the users themselves. The goal is to determine how many users returned for a subsequent session within a specified timeframe (e.g., 30 days) after their initial session. The final output should show the total number of users, the number of retained users, and the calculated retention rate.

1. **Identify Initial Sessions**: Use a Common Table Expression (CTE) to find the first session date for each user.
2. **Join Tables**: Join the sessions table with the users table to enrich the user data.
3. **Calculate Retention**: For each user, check if they have another session within the retention period (e.g., 30 days) after their first session.
4. **Aggregate Results**: Count total users, retained users, and compute the retention rate.
5. **Handle NULLs and Duplicates**: Ensure to manage NULL values in user IDs and avoid counting duplicate sessions.

```sql
WITH FirstSession AS (
    SELECT user_id, MIN(session_date) AS first_session_date
    FROM sessions
    GROUP BY user_id
),
RetainedUsers AS (
    SELECT fs.user_id,
           CASE WHEN s.session_date IS NOT NULL THEN 1 ELSE 0 END AS is_retained
    FROM FirstSession fs
    LEFT JOIN sessions s ON fs.user_id = s.user_id
    AND s.session_date > fs.first_session_date
    AND s.session_date <= DATE_ADD(fs.first_session_date, INTERVAL 30 DAY)
)
SELECT COUNT(DISTINCT user_id) AS total_users,
       SUM(is_retained) AS retained_users,
       (SUM(is_retained) / COUNT(DISTINCT user_id)) * 100 AS retention_rate
FROM RetainedUsers;
```

To optimize this query for large datasets: 1. **Indexing**: Create indexes on `user_id` and `session_date` in both the sessions and users tables to speed up joins and date comparisons. 2. **Limit Data Scanned**: If applicable, filter the sessions table by date range before performing the join to reduce the number of records processed. 3. **Batch Processing**: Consider breaking down the query into smaller parts if the dataset is exceptionally large, processing users in batches.

1. **NULL handling**: Ensure user IDs in sessions are not NULL; if they are, they should be excluded from the analysis. 2. **Duplicates**: Handle duplicate session entries by using `DISTINCT` in the CTE or by ensuring session data is clean before aggregation. 3. **No Sessions**: Users with no sessions should not be counted; this is managed by the LEFT JOIN and ensuring we only count users with sessions. 4. **Outliers**: If there are extreme session dates, consider filtering out sessions that fall outside a reasonable range (e.g., future dates).

Join sessions and clicks to find retention 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