Gamers Analysis – 1

Write an SQL query and identify first login date for each gamer.

---

 

---

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 first login_date of each gamer 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 
    player_id, 
    MIN(event_date) AS first_login
FROM
    gamers_activity
GROUP BY player_id
;