Search code examples
mysqlsqlgreatest-n-per-group

SQL: Extract First Two Days Records for Distinct Players

So I'm working on a table like below. This table contains information about a game player. I want to extract only the first 2-day records of each player group by their register day

-----------------------------------------------------------------------
|   player_id   |    first_timestamp   |   last_timestamp    |  spend |
-----------------------------------------------------------------------
|  1000000000   | 2020-07-08 08:30:21  | 2020-07-08 09:16:11 |  12.26 |
-----------------------------------------------------------------------
|  1000000000   | 2020-07-09 18:10:01  | 2020-07-09 18:21:07 |  24.27 |
-----------------------------------------------------------------------
|  1000000000   | 2020-07-09 20:30:21  | 2020-07-08 21:06:11 |  15.22 |
-----------------------------------------------------------------------
|  1000000000   | 2020-07-10 13:21:45  | 2020-07-08 14:00:31 |  13.57 |
-----------------------------------------------------------------------
|  1000000001   | 2020-07-09 15:07:09  | 2020-07-09 15:59:50 |  30.28 |
-----------------------------------------------------------------------

I would like the response table as below, so the tables will contains first 2-day records including their register day.

-----------------------------------------------------------------------
|   player_id   |    first_timestamp   |   last_timestamp    |  spend |
-----------------------------------------------------------------------
|  1000000000   | 2020-07-08 08:30:21  | 2020-07-08 09:16:11 |  12.26 |
-----------------------------------------------------------------------
|  1000000000   | 2020-07-09 18:10:01  | 2020-07-09 18:21:07 |  24.27 |
-----------------------------------------------------------------------
|  1000000000   | 2020-07-09 20:30:21  | 2020-07-08 21:06:11 |  15.22 |
-----------------------------------------------------------------------
|  1000000001   | 2020-07-09 15:07:09  | 2020-07-09 15:59:50 |  30.28 |
-----------------------------------------------------------------------

How shall I achieve this in SQL query? Thanks in advance.

Solution

  • Here is one option, using analytic functions:

    WITH cte AS (
    SELECT *, DENSE_RANK() OVER (PARTITION BY player_id ORDER BY DATE(first_timestamp)) dr
    FROM yourTable
)

SELECT player_id, first_timestamp, last_timestamp, spend
FROM cte
WHERE dr <= 2;

    Note that we need a ranking function here rather than ROW_NUMBER because a given date could occur more than once.