7. Game Play Analysis I

Problem

Table: Activity


+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| player_id    | int     |
| device_id    | int     |
| event_date   | date    |
| games_played | int     |
+--------------+---------+
(player_id, event_date) is the primary key (combination of columns with unique values) of this table.
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.

Problem Definition

Write a solution to find the first login date for each player.

Example

1706519651994900 Image scaled to 60%

Output

1706519690522030 Image scaled to 50%

Try It Yourself

Database Exercise

Database Schema:


-- Database schema would be rendered here

Exercise Script:


-- Exercise script would be rendered here

Available actions: Execute

Solution

The approach is to select the player_id and find the minimum event_date (representing the first login) for each player from the Activity table. The results can then grouped by player_id, providing insights into the earliest login date for each player.


SELECT player_id,
       Min(event_date) AS first_login
FROM   Activity
GROUP  BY player_id

Let’s break down the query step by step:

Step 1: Select fields:


SELECT player_id,
       Min(event_date) AS first_login
FROM   Activity

The SELECT clause specifies the columns that will be included in the result set. In this case, it selects the player_id and calculates the minimum (earliest) event_date for each player.

Output After Step 1:


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

Step 2: GROUP BY player_id:


GROUP  BY player_id

The GROUP BY clause is used to group the results by a specific column or columns. In this query, it groups the activity data by the player_id.

The min(event_date) function is then applied to each group separately, calculating the earliest login date for each distinct player_id.

Final Output:


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