Unable to join queries

Question

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!

Hi Gordon, sorry about that. I added it to the question. Let me know if it helps or more info is needed. — Se_7_eN, Jun 28 '20 at 17:04
You may not have sufficient reputation to upvote, But as the poster of the question you DO have the right to accept an answer. If unsure how then take the [tour](https://stackoverflow.com/tour). Since @Gordon answered the question to your satisfaction you should accept it. Doing so also helps future questioners having the or similar issues seeing an accepted answer. — Belayer, Jun 30 '20 at 01:28

Gordon Linoff · Accepted Answer · 2020-06-28T21:33:39.033

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;

Hey Gordon, not sure what I was doing earlier but I ran this query again and it worked perfectly... THANK YOU!!! (I don't have enough reputation to upvote or I would). — Se_7_eN, Jun 29 '20 at 01:14

Unable to join queries

1 Answers1