Description
Write a SQL query to find the first login date for each player.
Table:
activity| Column | Type |
|---|---|
| player_id | INT |
| device_id | INT |
| event_date | TEXT |
| games_played | INT |
Examples
Input:
CREATE TABLE activity (player_id INT, device_id INT, event_date TEXT, games_played INT); INSERT INTO activity VALUES (1, 2, '2016-03-01', 5), (1, 2, '2016-05-02', 6);Output:
1|2016-03-01Explanation:
Player 1 logged in on 2016-03-01 and 2016-05-02. The MIN date is 2016-03-01, which is their first login.
Input:
CREATE TABLE activity (player_id INT, device_id INT, event_date TEXT, games_played INT); INSERT INTO activity VALUES (5, 1, '2019-01-15', 3), (5, 2, '2019-01-10', 2), (6, 3, '2020-12-25', 1), (7, 1, '2018-06-01', 4), (7, 2, '2018-05-15', 1), (7, 3, '2018-07-20', 6);Output:
5|2019-01-10
6|2020-12-25
7|2018-05-15Explanation:
Player 5: dates 2019-01-15 and 2019-01-10, MIN = 2019-01-10. Player 6: only date 2020-12-25, MIN = 2020-12-25. Player 7: dates 2018-06-01, 2018-05-15, 2018-07-20, MIN = 2018-05-15 (earliest chronologically).
Input:
CREATE TABLE activity (player_id INT, device_id INT, event_date TEXT, games_played INT); INSERT INTO activity VALUES (10, 5, '2021-02-14', 0), (11, 6, '2021-03-01', 8), (11, 7, '2021-02-28', 3), (12, 8, '2020-01-01', 10);Output:
10|2021-02-14
11|2021-02-28
12|2020-01-01Explanation:
Player 10: only date 2021-02-14. Player 11: dates 2021-03-01 and 2021-02-28, MIN = 2021-02-28 (February 28 comes before March 1). Player 12: only date 2020-01-01. MIN(event_date) returns the earliest login for each player.
Constraints
- •
Use GROUP BY with MIN