Solving LeetCode SQL Problem 511: Game PlayAnalysis 1

You can find the problem on LeetCode’s website at this link. The problem statement is as follows:

This problem involves identifying the first login date for each player.

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

To solve this problem, using the aggregate function MIN() can be very useful to filter the earliest date in the event date, along with GROUP BY on player_id. This way, it will return the earliest date using MIN() for each player.

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

Final output:
| player_id | first_login |
| --------- | ----------- |
| 1         | 2016-03-01  |
| 2         | 2017-06-25  |
| 3         | 2016-03-02  |

There is another solution using the Window Function (link to mysql documentation). A window function performs an operation similar to an aggregate on a set of query rows. While GROUP BY consolidates the result into a single row, a window function produces a result for each query row. There is a non-aggregate function FIRST_VALUE() which we can use in conjunction with OVER() to obtain the first value of each aggregated result.

SELECT DISTINCT
  player_id,
  FIRST_VALUE(event_date) OVER (
    PARTITION BY
      player_id
    ORDER BY
      event_date
  ) AS first_login
FROM
  Activity;

Final Output:
| player_id | first_login |
| --------- | ----------- |
| 1         | 2016-03-01  |
| 2         | 2017-06-25  |
| 3         | 2016-03-02  |