LeetCode 534. Game Play Analysis III SQL Solution
Problem
LeetCode SQL Problem
- Game Play Analysis III
Activity table
| player_id | device_id | event_date | games_played |
|---|---|---|---|
| 1 | 2 | 2016-03-01 | 5 |
| 1 | 2 | 2016-05-02 | 6 |
| 1 | 3 | 2017-06-25 | 1 |
| 3 | 1 | 2016-03-02 | 0 |
| 3 | 4 | 2018-07-03 | 5 |
Solution - Correlated Subquery
- MySQL
- TSQL
Game Play Analysis III
SELECT A.player_id
,A.event_date
,(
SELECT sum(B.games_played)
FROM Activity B
WHERE B.player_id = A.player_id
AND B.event_date <= A.event_date
) AS games_played_so_far
FROM Activity AS A
ORDER BY A.player_id
,A.event_date
Game Play Analysis III
SELECT A.player_id
,A.event_date
,(
SELECT sum(B.games_played)
FROM Activity B
WHERE B.player_id = A.player_id
AND B.event_date <= A.event_date
) AS games_played_so_far
FROM Activity AS A
ORDER BY A.player_id
,A.event_date
Query Output
| player_id | event_date | games_played_so_far |
|---|---|---|
| 1 | 2016-03-01 | 5 |
| 1 | 2016-05-02 | 11 |
| 1 | 2017-06-25 | 12 |
| 3 | 2016-03-02 | 0 |
| 3 | 2018-07-03 | 5 |
Solution - Self Join
- MySQL
- TSQL
Game Play Analysis III
SELECT A.player_id
,A.event_date
,sum(B.games_played) AS games_played_so_far
FROM Activity AS A
INNER JOIN Activity AS B ON A.player_id = B.player_id
AND A.event_date >= B.event_date
GROUP BY A.player_id
,A.event_date
ORDER BY A.player_id
,A.event_date
Game Play Analysis III
SELECT A.player_id
,A.event_date
,sum(B.games_played) AS games_played_so_far
FROM Activity AS A
INNER JOIN Activity AS B ON A.player_id = B.player_id
AND A.event_date >= B.event_date
GROUP BY A.player_id
,A.event_date
ORDER BY A.player_id
,A.event_date
Query Output
| player_id | event_date | games_played_so_far |
|---|---|---|
| 1 | 2016-03-01 | 5 |
| 1 | 2016-05-02 | 11 |
| 1 | 2017-06-25 | 12 |
| 3 | 2016-03-02 | 0 |
| 3 | 2018-07-03 | 5 |
Solution - Sum() Window Function
- MySQL
- TSQL
Game Play Analysis III
SELECT player_id
,event_date
,sum(games_played) OVER (
PARTITION BY player_id ORDER BY event_date
) AS games_played_so_far
FROM Activity
Game Play Analysis III
SELECT player_id
,event_date
,sum(games_played) OVER (
PARTITION BY player_id ORDER BY event_date
) AS games_played_so_far
FROM Activity
Query Output
| player_id | event_date | games_played_so_far |
|---|---|---|
| 1 | 2016-03-01 | 5 |
| 1 | 2016-05-02 | 11 |
| 1 | 2017-06-25 | 12 |
| 3 | 2016-03-02 | 0 |
| 3 | 2018-07-03 | 5 |