8/3每日一題(MYSQL 回傳玩家第一次登入時間)

Table: Activity

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| player_id    | int     |
| device_id    | int     |
| event_date   | date    |
| games_played | int     |
+--------------+---------+
In SQL, (player_id, event_date) is the primary key 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.

 

Find the first login date for each player.

Return the result table in any order.

The result format is in the following example.

 

Example 1:

Input: 
Activity table:
+-----------+-----------+------------+--------------+
| 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            |
+-----------+-----------+------------+--------------+
Output: 
+-----------+-------------+
| player_id | first_login |
+-----------+-------------+
| 1         | 2016-03-01  |
| 2         | 2017-06-25  |
| 3         | 2016-03-02  |
+-----------+-------------+

# Write your MySQL query statement below

SELECT A1.player_id, MIN(A1.event_date) AS first_login

#選擇輸出player_id 和該玩家首次登入日期

FROM Activity A1

GROUP BY player_id

#以玩家id分組以便顯示每個玩家首次登日日期

---------參考答案

/* Write your PL/SQL query statement below */
SELECT player_id, TO_CHAR( MIN(event_date),'YYYY-MM-DD') 
AS first_login FROM Activity GROUP BY player_id

TO_CHAR(MIN(event_date),'YYYY-MM-DD') :

使用TO_CHAR()將MIN(eevent_date)找出玩家首次登入時間轉換成'YYYY-MM-DD'格式字串輸出

GROUP BY player_id :

使用玩家編號進行分組,確保每個玩家都只有一行紀錄,並且MIN(enent_date)會計算首次登入日期

組合再一起就是初出每位玩家的首次登入紀錄