Player Session Data SQL Examples

As part of a technical take-home assessment, I was presented with the following three tables and asked to manipulate them using SQL.

Article Summary

Domain: Gaming Data Engineering & Technical SQL Assessment

Core Objective: Demonstrate production-grade SQL data manipulation, analytical CTE structuring, timestamp/epoch arithmetic, window function navigation, and dynamic Jinja/dbt metaprogramming across player telemetry tables.

Methodology: Explicit type casting (::INT, TO_TIMESTAMP), defensive joining (LEFT JOIN to identify orphan records and unobserved churn buckets), windowed LEAD() event intervals, and generate_series() array expansions for dynamic unpivoting.

Key Solutions:

1. 1. 1. 1. Data Cleaning & Aggregation: Materialized cleaned timestamp views and computed active session lengths using epoch difference extraction (EXTRACT(EPOCH FROM ...)).

         2. Windowed Inter-Event Timing: Deployed LEAD(ts, 1) OVER (PARTITION BY player_id, session_id) to isolate intra-session action latencies while explicitly flagging stakeholder ambiguity regarding grouping granularities.

         3. Dynamic Pivot & Metaprogramming: Handled sparse dimensional data (missing Level 3 churn) via COALESCE with generate_series(), and provided a scalable dbt/Jinja macro solution to dynamically unpivot rows to columns based on target table invariants.

Table name: player

Table name: session

Table name: churn

1. Create a view which selects average gold inventory per level (rank) for users who joined after 3/3/2014, reaching rank higher than 10,  where average gold is below 1000.

CREATE VIEW avg_gold_per_level AS
    SELECT 
        rank::INT AS rank,
        AVG(gold) AS avg_gold
    FROM player 
    WHERE TO_TIMESTAMP(joined_date, 'DD/MM/YYYY HH24:MI')::DATE > '2014-03-03'::DATE
        AND rank::INT > 10
    GROUP by rank::INT
    HAVING AVG(gold) < 1000; 

2. Select all session records for users reaching rank higher than 50.

WITH 

users_higher_50 AS (
    SELECT 
        player_id::INT AS player_id
    FROM player 
    WHERE rank::INT > 50
)

SELECT 
    users.player_id, 
    sessions.session_id, 
    sessions.platform, 
    sessions.install::BOOLEAN AS install, 
    TO_TIMESTAMP(sessions.ts, 'DD/MM/YYYY HH24:MI') AS ts, 
    TO_DATE(sessions.date_utc, 'DD/MM/YYYY') AS date_utc
FROM users_higher_50 AS users  
INNER JOIN session AS sessions 
    ON users.player_id = sessions.player_id::INT
ORDER BY users.player_id, sessions.session_id, ts DESC; 

3. Select average session length for users reaching rank higher than 50.

WITH 

users_higher_50 AS (
    SELECT 
        player_id::INT AS player_id
    FROM player 
    WHERE rank::INT > 50
),

sessions_cleaned AS (
    SELECT 
        users.player_id, 
        sessions.session_id::INT AS session_id, 
        TO_TIMESTAMP(sessions.ts, 'DD/MM/YYYY HH24:MI') AS ts_cleaned
    FROM users_higher_50 AS users  
    INNER JOIN session AS sessions 
        ON users.player_id = sessions.player_id::INT
),

sessions_agg AS (
    SELECT 
        player_id, 
        session_id, 
        MIN(ts_cleaned) AS session_start_ts, 
        MAX(ts_cleaned) AS session_end_ts
    FROM sessions_cleaned 
    GROUP BY player_id, session_id 
),

session_length AS (
    SELECT 
        player_id, 
        session_id, 
        EXTRACT(EPOCH FROM (session_end_ts - session_start_ts)) AS session_length_seconds 
    FROM sessions_agg 
)

SELECT 
    AVG(session_length_seconds) AS avg_session_length_seconds, 
    AVG(session_length_seconds) / 60 AS avg_session_length_minutes
FROM session_length; 

4. Select all users who don’t have any session record (they don’t appear in session table)

SELECT 
    player.player_id::INT AS player_id
FROM player
LEFT JOIN session 
    ON player.player_id::INT = session.player_id::INT
WHERE session.session_id IS NULL;

5. Select average time for player’s actions. A player’s action lasts until the next session record of a player id, having the same session id.

WITH 

sessions_cleaned AS (
    SELECT 
        player_id::INT AS player_id, 
        session_id::INT AS session_id, 
        TO_TIMESTAMP(ts, 'DD/MM/YYYY HH24:MI') AS ts_cleaned
    FROM session
),

sessions_previous_ts AS (
    SELECT 
        player_id, 
        session_id, 
        ts_cleaned AS action_ts, 
        LEAD(ts_cleaned, 1) OVER (PARTITION BY player_id, session_id ORDER BY ts_cleaned ASC) AS next_action_ts
    FROM sessions_cleaned 
),

session_time_between_actions AS (
    SELECT 
        player_id, 
        session_id, 
        EXTRACT(EPOCH FROM (next_action_ts - action_ts)) AS seconds_between_actions 
    FROM sessions_previous_ts 
    WHERE next_action_ts IS NOT NULL
)

SELECT 
    player_id, 
    -- session_id -- The example is ambiguous as to whether this is a per-player or per-player-per-session metric, so I'm leaving this here but commented out. I would ask for clarification from stakeholders.
    AVG(seconds_between_actions) AS avg_time_between_actions_seconds, 
    AVG(seconds_between_actions) / 60 AS avg_time_between_actions_minutes
FROM session_time_between_actions
GROUP BY player_id; --, session_id 

6. Taking into consideration Churn Sheet, pivot the data to have the Level on rows instead of columns. Level 3 should also appear in the results with 0 value.

WITH 

max_level AS (
    SELECT 
    MAX(level::INT) AS max_level 
    FROM Churn
),

all_levels AS (
    SELECT 
        generate_series(1, (SELECT max_level FROM max_level)) AS level
),

churn_complete AS (
    -- Left join ensures level 3 exists with a count of 0
    SELECT 
        all_levels.level,
        COALESCE(Churn.count_of_churned_players, 0) AS count_of_churned_players
    FROM all_levels
    LEFT JOIN churn 
        ON all_levels.level = churn.level::INT
)

SELECT 
    SUM(CASE WHEN level = 1 THEN count_of_churned_players END) AS level_1,
    SUM(CASE WHEN level = 2 THEN count_of_churned_players END) AS level_2,
    SUM(CASE WHEN level = 3 THEN count_of_churned_players END) AS level_3,
    SUM(CASE WHEN level = 4 THEN count_of_churned_players END) AS level_4,
    SUM(CASE WHEN level = 5 THEN count_of_churned_players END) AS level_5
FROM churn_complete;

For dynamic situations, I would utilize dbt and jinja to do this:

{% set max_level_query %}
    SELECT MAX(level::INT) FROM churn
{% endset %}

{% if execute %}
    {% set max_level = run_query(max_level_query).columns[0][0] %}
{% else %}
    {% set max_level = 5 %}
{% endif %}

WITH max_level AS (
    SELECT MAX(level::INT) AS max_lvl FROM churn
),

all_levels AS (
    SELECT generate_series(1, (SELECT max_lvl FROM max_level)) AS level
),

churn_complete AS (
    SELECT 
        all_levels.level,
        COALESCE(churn.count_of_churned_players, 0) AS count_of_churned_players
    FROM all_levels
    LEFT JOIN churn
        ON all_levels.level = churn.level::INT
)

SELECT 
    {% for lvl in range(1, max_level + 1) %}
        SUM(CASE WHEN level = {{ lvl }} THEN count_of_churned_players END) AS level_{{ lvl }}{% if not loop.last %},{% endif %}
    {% endfor %}
FROM churn_complete;