7. Game Play Analysis I

Grokking SQL for Tech Interviews

Ask Author

Back to course home

0% completed

Vote For New Content

7. Game Play Analysis I

Table of Contents

Contents are not accessible

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

Output

Try It Yourself

MYSQL

. . . .

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  |
+-----------+-------------+

.....

Like the course? Get enrolled and start learning!

Table of Contents

Contents are not accessible