SQLPad
Sign In

215. Monthly active users

easy roblox

********************************************

**********************************************************

Table: roblox_session

User sessions log.

   col_name              | col_type
-------------------------+--------------------------
  session_id             | uuid
  user_id                | uuid
  started_at             | timestamp
  ended_at               | timestamp

Sample results 

 year | month | monthly_active_users
------+-------+----------------------
 2023 |     3 |                 1856
 2023 |     4 |                 3534

Solution postgres

SELECT
    EXTRACT(YEAR FROM started_at) AS year,
    EXTRACT(MONTH FROM started_at) AS month,
    COUNT(DISTINCT user_id) AS monthly_active_users
FROM
    roblox_session
WHERE
    EXTRACT(EPOCH FROM (ended_at - started_at)) >= 10
GROUP BY
    EXTRACT(YEAR FROM started_at),
    EXTRACT(MONTH FROM started_at)
ORDER BY
    year, month;

Explanation

This query is used to find the number of unique active users per month for sessions that lasted at least 10 seconds in the 'roblox_session' table.

Here is a breakdown of the query:

  • EXTRACT(YEAR FROM started_at) AS year, EXTRACT(MONTH FROM started_at) AS month : This part of the query is extracting the year and the month from the 'started_at' column.

  • COUNT(DISTINCT user_id) AS monthly_active_users : This part is counting the unique user ids, which represents the number of unique active users for each month.

  • FROM roblox_session : This is the table where the data is coming from.

  • WHERE EXTRACT(EPOCH FROM (ended_at - started_at)) >= 10 : This condition is specifying that we are only interested in sessions that lasted at least 10 seconds. The 'EPOCH' extracts the number of seconds since '1970-01-01 00:00:00+00' (the Unix epoch) from the interval (ended_at - started_at).

  • GROUP BY EXTRACT(YEAR FROM started_at), EXTRACT(MONTH FROM started_at) : This is grouping the results by year and month, which means the count of unique users will be for each month of each year.

  • ORDER BY year, month : This is ordering the results by year and month. The results will be displayed starting from the earliest year and month to the latest.

In summary, this query provides the count of unique active users per month for sessions that lasted at least 10 seconds.

Expected results


More Roblox questions

ID Title Level FTPR
214 D30 (30days) retention rate roblox medium
20%
216 Most active customers roblox hard
10%
217 Most engaged customers roblox hard
10%