Combining multiple queries with 4 joins?

Question

I have a query I run in my ti_userevent database that combines users play session lengths from multiple play sessions and instead returns the total play length for each player between a set time and date (Instead of 4 sessions for Player 1, it returns 1 and gives me the total play length of all 4 sessions).

This query allows me to set the date and time range for the data I am looking for in MetaBase, and returns the player ID, player name and total play length across multiple sessions for each user within the timeframe provided.

This is the query.

SELECT t2.playername,
    (EXTRACT(EPOCH
        FROM ( MAX(t.local_time) - MIN(t.local_time)) ::INTERVAL)/60)::integer as duration
            FROM ti_userevent t
        JOIN
    (SELECT DISTINCT t2.value as playerName, t2.session_id
            FROM ti_userevent t2
                WHERE context = 'Player' 
                AND
                action = 'Name'
                    [[AND local_time >= {{StartTime}}::timestamp at time zone 'US/Pacific']]
                    [[AND local_time < {{EndTime}}::timestamp at time zone 'US/Pacific']]
            ) t2
        ON t2.session_id = t.session_id
GROUP BY t2.playername;

Example Data:

   User ID        User Name   Play Length of all sessions
    ID1             Name 1          105
    ID2             Name 2          215
    ID3             Name 3          352

But, I would also like to get their average FPS, GPU and Frame performance from the Query. The problem is, these are all stored in the same table!

In order to get the average GPU performance I run the following, this only gives me the average performance based on the build number.

SELECT
    session_id
    , value::decimal as AvgGpuMs
FROM 
    ti_userevent
WHERE 
        context = 'Perf'
    AND action = 'GpuMs'
    AND session_id IN 
        (SELECT DISTINCT session_id
            FROM 
                ti_userevent
            WHERE 
                action = 'BuildNum'
                and value = {{BuildNum}})

Example Data:

user_id    session_id    avggpums
ID1         session1       8.2
ID2         session1       8.7
ID3         session1       9.7

I have to run the same query above for the AvgFPS and AvgFrameMs performance.

What I would like is to combine the 3 AvgFPS, AvgFrameMs and AvgGPUms queries into the first Play session length query. I would like to have them all run off the timeframe range in the first query... I have tried several times and am having a hard time getting it to return the data I am after.

I am not the best with SQL so I am hoping someone on here could help me. Thanks for all your help!

Answer 1

Consider a combination of conditional calculation at session level and then averaging at player level. Also, use CTEs for readability between underlying sources.

NOTE: Assumptions are made in defining FPS and Frame and may need adjustment.

WITH session_time AS (
    SELECT DISTINCT value as playerName
           , session_id
    FROM ti_userevent
    WHERE context = 'Player' 
      AND action = 'Name'
      [[AND local_time >= {{StartTime}}::timestamp at time zone 'US/Pacific']]
      [[AND local_time < {{EndTime}}::timestamp at time zone 'US/Pacific']]
), session_metrics AS (
    SELECT session_id
          , AVG((CASE WHEN context='Perf' AND action='GpuMs' THEN value END)::decimal) AS GpuMs
          , AVG((CASE WHEN context='Perf' AND action='FPS'   THEN value END)::decimal) AS FPS
          , AVG((CASE WHEN context='Perf' AND action='Frame' THEN value END)::decimal) AS Frame
    FROM ti_userevent
    WHERE session_id IN 
       (SELECT session_id
        FROM ti_userevent
        WHERE action = 'BuildNum'
          AND value = {{BuildNum}})
    GROUP BY session_id
)

SELECT t.playername
       , (EXTRACT(EPOCH FROM (MAX(ue.local_time) - MIN(ue.local_time))::INTERVAL)/60)::integer AS duration
       , AVG(m.GpuMs) AS AvgGpuMs
       , AVG(m.FPS)   AS AvgFPS
       , AVG(m.Frame) AS AvgFrame
 
FROM ti_userevent ue
INNER JOIN session_time t 
   ON ue.session_id = t.session_id
INNER JOIN session_metrics m
   ON ue.session_id = m.session_id
GROUP BY t.playername;

By the way, your table appears to be an entity-attribute value model (i.e., rows in keyword-value pairs). While there is much discussion if this is an optimal design, most simple queries in a relational, normalized model will become more complex in EAV model as demonstrated above. In a relational framework, you would have two tables for users and sessions in a one-to-many relationship.