Game Play Analysis I is a very important question that is being asked in many company interviews. 

Before getting started first we have to understand the question properly. for that, we need SQL Schema and questions to find the required output.


Let's start with the SQL Schema of the table named Activity:


CREATE TABLE Activity (player_id int, device_id int, event_date date, games_played int)


INSERT INTO Activity (player_id, device_id, event_date, games_played) values ('1', '2', '2016-03-01', '5')

INSERT INTO Activity (player_id, device_id, event_date, games_played) values ('1', '2', '2016-05-02', '6')

INSERT INTO Activity (player_id, device_id, event_date, games_played) values ('2', '3', '2017-06-25', '1')

INSERT INTO Activity (player_id, device_id, event_date, games_played) values ('3', '1', '2016-03-02', '0')

INSERT INTO Activity (player_id, device_id, event_date, games_played) values ('3', '4', '2018-07-03', '5')


The Activity table will look like this: 


player_id device_id event_date games_played
1 2 2016-03-01 5
1 2 2016-05-02 6
2 3 2017-06-25 1
3 1 2016-03-02 0
3 4 2018-07-03 5


This table shows the activity of players of some games.

Each row is a record of a player who logged in and played a number of games (possibly 0) before logging out on someday using some device.


Question: Write an SQL query to report the first login date for each player.

The query result will look like this:


player_id first_login
1 2016-03-01
2 2017-06-25
3 2016-03-02


Now, we have question and what we want to achieve. Let's begin with the SQL algorithm,

As we can see we require a minimum date from event_date based on player_id and also event_date is presented as the first_login column name.

So the query will be,

SELECT player_id, MIN(event_date) as first_login FROM Activity GROUP BY player_id;

This is the required query to solve the above question.

I hope you understood. Please let me know in the comments about your way of doing this question. 

If you have any question please write in the comment section.