- ************************
- *******************************************************
- ************************************************************************************************************************
- **********************************************************************************
- ************************************************************
Table 1: roblox_account
Roblox account creation records.
col_name | col_type -------------------------+-------------------------- user_id | uuid created_at | timestamp
Table 2: roblox_session
User sessions log.
col_name | col_type -------------------------+-------------------------- session_id | uuid user_id | uuid started_at | timestamp ended_at | timestamp
Sample results
retention_rate --------------------- 27.2727272727272727
Solution postgres
WITH new_users AS (
-- Users who created accounts on April 1, 2023
SELECT DISTINCT user_id
FROM roblox_account
WHERE DATE(created_at) = '2023-04-01'
),
active_users AS (
-- Users from the April 1 cohort who had sessions on April 30, 2023
-- lasting at least 10 seconds
SELECT DISTINCT user_id
FROM roblox_session
WHERE DATE(started_at) = '2023-04-30'
AND EXTRACT(EPOCH FROM (ended_at - started_at)) >= 10
AND user_id IN (SELECT user_id FROM new_users)
)
-- Calculate D30 retention rate
SELECT
(SELECT COUNT(*) FROM active_users) * 100.0/
(SELECT COUNT(*) FROM new_users) AS retention_rate;
Explanation
This query calculates the D30 retention rate for users who created accounts on Roblox on April 1, 2023. The D30 retention rate is the percentage of users who are still active on the platform 30 days after their account creation.
The query is divided into two parts using Common Table Expressions (CTEs) or "WITH" clauses for better readability and maintenance.
The first CTE, new_users, retrieves the unique identifiers (user_id) of users who created their account on April 1, 2023.
The second CTE, active_users, retrieves the unique identifiers (user_id) of users from the new_users cohort who had a session on April 30, 2023 (i.e., 30 days later) that lasted at least 10 seconds.
The final part of the query calculates the D30 retention rate by dividing the number of active_users by the number of new_users and multiplying by 100 to get the percentage. If the rate is high, it means a lot of new users have stayed active on the platform, which is a good sign of user engagement.
Expected results
More Roblox questions
| ID | Title | Level | FTPR |
|---|---|---|---|
| 215 | Monthly active users roblox | easy |
|
| 216 | Most active customers roblox | hard |
|
| 217 | Most engaged customers roblox | hard |
|