Write a SQL query that identify the device that was first used to log in for each gamer.
Input Table: gamers_activity
player_id | device_id | event_date | games_played |
7 | 22 | 2017-04-02 | 4 |
7 | 22 | 2017-06-05 | 3 |
9 | 33 | 2018-02-26 | 0 |
6 | 11 | 2017-05-07 | 7 |
6 | 44 | 2019-08-06 | 3 |
Output Table:
player_id | device_id |
7 | 22 |
9 | 33 |
6 | 11 |
Explanation:
“gamers_activity” table shows the activity of gamers who played any game. Each row represents a record of gamer who played a game on that particular date before logging out. He might not have played any game as well ( that’s why we have one record with 0 as games_played in input table).
Identify device_id used by gamer on first login_date irrespective of whether he played any game or not on very first day.
DDLs:
CREATE TABLE IF NOT EXISTS gamers_activity ( player_id INT, device_id INT, event_date DATE, games_played INT ); INSERT INTO gamers_activity VALUES (7, 22, '2017-04-02', 4); INSERT INTO gamers_activity VALUES (7, 22, '2017-06-05', 3); INSERT INTO gamers_activity VALUES (9, 33, '2018-02-26', 0); INSERT INTO gamers_activity VALUES (6, 11, '2017-05-07', 7); INSERT INTO gamers_activity VALUES (6, 44, '2019-08-06', 3);
Solution:
SELECT A.player_id, A.device_id FROM activity A JOIN ( SELECT player_id, MIN(event_date) AS event_date FROM activity GROUP BY player_id ) B ON A.player_id = B.player_id AND A.event_date = B.event_date ;