SQLPad
Sign In

216. Most active customers

hard 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 

               user_id                | year | month | active_sessions_count
--------------------------------------+------+-------+-----------------------
 bd4060c6-b5bd-48c7-bb74-8082f6255590 | 2023 |     3 |                    29
 6ce61b14-9844-43d1-aee5-3b19cc66f40b | 2023 |     3 |                    29
 da2a61e0-0062-40ab-ae2b-3e45a6ff8047 | 2023 |     3 |                    28

Solution postgres

WITH monthly_active_sessions AS (
    SELECT
        user_id,
        EXTRACT(YEAR FROM started_at) AS year,
        EXTRACT(MONTH FROM started_at) AS month,
        COUNT(*) AS active_sessions_count
    FROM
        roblox_session
    WHERE
        EXTRACT(EPOCH FROM (ended_at - started_at)) > 10
    GROUP BY
        user_id,
        EXTRACT(YEAR FROM started_at),
        EXTRACT(MONTH FROM started_at)
)

, rankings AS (
    SELECT
        user_id,
        year,
        month,
        active_sessions_count,
        RANK() OVER(PARTITION BY year, month ORDER BY active_sessions_count DESC) AS rnk
    FROM
        monthly_active_sessions
)

SELECT
    user_id,
    year,
    month,
    active_sessions_count
FROM
    rankings
WHERE
    rnk <= 3
ORDER BY
    year, month, rnk;

Explanation

This query is used to find the top 3 users with the most active sessions each month. Active sessions are defined as sessions that lasted more than 10 seconds.

The query is broken down into three parts:

  1. The first part (monthly_active_sessions) selects user IDs and the year and month of the session from the roblox_session table, but only for sessions that lasted more than 10 seconds (as defined by the WHERE clause). It also counts the number of such sessions for each user each month (as defined by the GROUP BY clause).

  2. The second part (rankings) ranks these users for each month based on the number of active sessions they had. The RANK() function is used to assign a rank to each user, with those having more active sessions getting a lower rank. The PARTITION BY clause ensures that this ranking is done separately for each month.

  3. The final part of the query selects the top 3 users for each month based on their rank. This is done by filtering for users with a rank of 3 or less. The result is then ordered by year, month, and rank.

Expected results


More Roblox questions

ID Title Level FTPR
214 D30 (30days) retention rate roblox medium
20%
215 Monthly active users roblox easy
80%
217 Most engaged customers roblox hard
10%