Gamers Analysis – 2

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
;

 

 

Leave a Comment