SQL: Extract First Two Days Records for Distinct Players

Question

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.

score 1 · Accepted Answer · answered Oct 28 '20 at 03:09

1

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.

answered Oct 28 '20 at 03:09

Tim Biegeleisen

451,927
24
239
318

That's awesome, but what about if I want to group by their register day, like I want to group all the players register on 2020-07-08, 2020-07-09 etc. – Mariana Chen Oct 28 '20 at 03:24
That's great, and it's a different question. I have answered the question you actually asked above. If you want to do some aggregation, it would be a very different query than what I wrote above. – Tim Biegeleisen Oct 28 '20 at 03:25

SQL: Extract First Two Days Records for Distinct Players

1 Answers1