Unable to join queries

I hope you’re able to help with this, it is creating a lot of unnecessary work for me…

I have a lot of users that are assigned a unique session_id each time they start the game. This means they can get multiple session_id ‘s in a single day if they crash and/or just restart the app. I want to be able to combine all these id’s in a single query without having to manually check each one.

First, I use the query below to get the list of users who played the game between a certain time frame. This gives me their user_id, session_id as PlayerName

SELECT
      user_id
    , session_id
    , value as PlayerName
FROM 
    bi_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']]
GROUP BY 1,2,3
ORDER BY PlayerName

    SAMPLE DATA (From query above)
user_id          session_id           playername
4234hjh2342      asjd7a7yf978as       Player 1
4234hjh2342      asjd7a7yf978as       Player 1
f872j23hasd      52354294khjhjh       Player 2
9as90d09asd      zf87fsd08s7das       Player 3
9as90d09asd      80asd7g90asd7g       Player 3
9as90d09asd      aga90786e9a0f6       Player 3

Now, the hard part… I have to manually enter each session_id to get their playtime against the server time. Since each player can have multiple session_id’s I allowed for the entry of multiple session_id’s for each player (As a variable in Metabase).

SELECT          
    (EXTRACT(EPOCH FROM ( MAX(local_time) - MIN(local_time)) ::INTERVAL)/60)::integer as duration             
FROM bi_userevent     
WHERE 
    session_id = {{session_id}}
    [[OR session_id = {{session_id2}}]]
    [[OR session_id = {{session_id3}}]]
    [[OR session_id = {{session_id4}}]]

Once I enter the session_id’s I run the query and it gives me the duration of playtime for the combined session_id’s. I can have somewhere up to 400 players in a single query and it just feels like a massive waste of time entering hundreds of session ID’s and it feels like a massive waste of time. I thought I would be able to add the duration statement from the second query into the first and group by the user_id… Simple right? No, I get a value of 0 and I am not sure why.

My ultimate goal is to run 1 query, return the player name and session_id length of play… Or, better yet join all of a single players session id’s together with the combined play length of the id’s

THANK YOU for any and all help, it is greatly appreciated!

Answer

From what you describe, you can just run one query. However, I’m not clear if you want one row:

SELECT (EXTRACT(EPOCH FROM ( MAX(local_time) - MIN(local_time)) ::INTERVAL)/60)::integer as duration             
FROM  sr_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']];

Or one row per player:

SELECT value as playername, 
       (EXTRACT(EPOCH FROM ( MAX(local_time) - MIN(local_time)) ::INTERVAL)/60)::integer as duration             
FROM  sr_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']]
GROUP BY value;

EDIT:

Based on your comment, a JOIN should work:

SELECT s2.playername, 
       (EXTRACT(EPOCH FROM ( MAX(s.local_time) - MIN(s.local_time)) ::INTERVAL)/60)::integer as duration             
FROM sr_userevent s JOIN
     (SELECT DISTINCT s2.value as playerName, s2.session_id
      FROM sr_user_event s2
      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']]
    ) s2
    ON s2.session_id = s.session_id
GROUP BY s2.playername;