Citizen Citizen - 4 days ago 5
SQL Question

Get prior row for each element in Where in

I am working with a PostgreSQL database.

Here the idea :


  • An user can play to a game

  • Each game can have 1 or more mini-games

  • Each mini-game have a result



Here the kind of database :

id | user_id | game_id | mini_game_id | result | created_at
-----------------------------------------------------------
70 | 44 | 105 | 22 | 19 | 28/11/2016
69 | 44 | 105 | 20 | 18 | 28/11/2016

68 | 44 | 104 | 22 | 17 | 27/11/2016
67 | 44 | 104 | 21 | 16 | 27/11/2016

66 | 44 | 103 | 22 | 15 | 26/11/2016
65 | 44 | 103 | 21 | 14 | 26/11/2016
64 | 44 | 103 | 20 | 13 | 26/11/2016


I want to show at the end of its latest game, beside result of each mini_game, results of the previous same mini_game.

Result needed :



Example : for
user 44
, at the end of its
game 105
, I want to obtain this data :

id | game_id | mini_game_id | second-to-last-result | date
-----------------------------------------------------------------
68 | 104 | 22 | 17 | 27/11/2016
64 | 103 | 20 | 13 | 26/11/2016


What I've tried :



First try :



SELECT mini_game_id,
array_agg(result) as results,
array_agg(created_at) as dates
FROM result
WHERE user_id = 44
AND game_id != 105 --Exclude latest game
GROUP BY mini_game_id
ORDER BY mini_game_id


Result :

mini_game_id | results | dates
--------------------------------------------------
22 | {17, 15} | {27/11/2016, 26/11/2016}
21 | {16, 14} | {27/11/2016, 26/11/2016}
20 | {13} | {26/11/2016}


Here, the problem is I get every result of each mini_game of each game for an user, and it seems overkill to me, because I can have thousands of results...

Second try :



SELECT id,
game_id,
mini_game_id,
result,
created_at
FROM result
WHERE user_id = 44
AND game_id != 105 --Exclude latest game
AND mini_game IN (22, 20) --The two mini-games the user have played in game 105
ORDER BY created_at DESC
LIMIT 1


Result :



id | game_id | mini_game_id | result | created_at
--------------------------------------------------
68 | 104 | 22 | 17 | 27/11/2016


Problem :



Obviously, I get only 1 result. But the idea I had was to
limit 1
on each value inside of the
WHERE IN


Can you help me understand how I could do this ?

Thanks

Answer
WITH cte AS (
    SELECT *
       ,DENSE_RANK() OVER (PARTITION by user_id ORDER BY created_at DESC) as LatestGame
       ,LAG(result) OVER (PARTITION BY user_id, mini_game_id ORDER BY created_at) as SecondToLastResult
    FROM
       Table
)

SELECT
    id
    ,game_id
    ,mini_game_id
    ,result
    ,SecondToLastResult
    ,created_at as Date
FROM
    cte
WHERE
    LatestGame = 1
;

Use DENSE_RANK() to define latest game and LAG() to get the previous result then select where LatestGame = 1 from the common table expression [cte]

Here is postgresql's link about window functions: https://www.postgresql.org/docs/8.4/static/functions-window.html

To filter to only 1 user you can do the following because you will no longer need to partition the window functions by user_id:

WITH cte AS (
    SELECT *
       ,DENSE_RANK() OVER (ORDER BY created_at DESC) as LatestGame
       ,LAG(result) OVER (PARTITION BY mini_game_id ORDER BY created_at) as SecondToLastResult
    FROM
       Table
    WHERE
       user_id = 44
)

And just for fun here is how you can do it with out window functions:

SELECT
    t.id
    ,t.user_id
    ,t.game_id
    ,t.mini_game_id
    ,t.result
    ,t.created_at
    ,(SELECT result
             FROM
                Table t2
            WHERE
                t.user_id = t2.user_id
                AND t.mini_game_id = t2.mini_game_id
                AND t.created_at > t2.created_at
            ORDER BY
                t2.created_at DESC
            LIMIT 1) as SecondToLastResult
FROM
    Table t
    LEFT JOIN Table t1
    ON t.user_id = t1.user_id
    AND t.created_at < t1.created_at
WHERE
    t1.id IS NULL
    AND t.user_id = 44

I would assume window functions will preform better for you though.

Comments