214. D30 (30days) retention rate

medium roblox

  • ************************
  • *******************************************************
  • ************************************************************************************************************************
  • **********************************************************************************
  • ************************************************************

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


Solution postgres

WITH new_users AS (
    -- Users who created accounts on April 1, 2023
    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
    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 COUNT(*) FROM active_users) * 100.0/
    (SELECT COUNT(*) FROM new_users)  AS retention_rate;


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