I want to configure row_number with a case condition. To look on "time_diffs" column and check - if there 1's go one by one, than it's a one group, the number should repeat when it proceeds after previous 1's. If there 0's, than each 0 is the one group by itself, and number won't repeat - it will grow on +1 after each 0's it proceeds. And when the itterator meets new 1's, after proceeding between 0's, it won't reset the counting. It will continue counting, +1 after 0's, but with the logic described above.
The query and result examples listed below.
select session_id,
player_id,
country,
start_time,
end_time,
case when timestampdiff(minute,
lag(end_time, 1) over(partition by player_id order by end_time)
, start_time) < 5 then 1
when timestampdiff(minute, end_time
, lead(start_time, 1) over(partition by player_id order by start_time)) < 5 then 1
else 0
end as time_diffs
/* , here is a new code with an expected result */
from game_sessions
where 1=1
and player_id = 1
order by player_id, start_time
The result of the current query:
| session_id | player_id | country | start_time | end_time | time_diffs |
|---|---|---|---|---|---|
| 1 | 1 | UK | 01.01.2021 00:01 | 01.01.2021 00:10 | 1 |
| 2 | 1 | UK | 01.01.2021 00:12 | 01.01.2021 01:24 | 1 |
| 13 | 1 | UK | 01.01.2021 01:27 | 01.01.2021 01:50 | 1 |
| 3 | 1 | UK | 01.01.2021 10:01 | 01.01.2021 15:10 | 0 |
| 16 | 1 | UK | 01.01.2021 17:10 | 01.01.2021 17:20 | 1 |
| 17 | 1 | UK | 01.01.2021 17:22 | 01.01.2021 17:55 | 1 |
| 54 | 1 | UK | 01.01.2021 18:15 | 01.01.2021 18:35 | 0 |
| 32 | 1 | UK | 01.01.2021 18:55 | 01.01.2021 19:35 | 0 |
What I expect to see with a new column added to the current query:
| session_id | player_id | country | start_time | end_time | time_diffs | expected_result |
|---|---|---|---|---|---|---|
| 1 | 1 | UK | 01.01.2021 00:01 | 01.01.2021 00:10 | 1 | 1 |
| 2 | 1 | UK | 01.01.2021 00:12 | 01.01.2021 01:24 | 1 | 1 |
| 13 | 1 | UK | 01.01.2021 01:27 | 01.01.2021 01:50 | 1 | 1 |
| 3 | 1 | UK | 01.01.2021 10:01 | 01.01.2021 15:10 | 0 | 2 |
| 16 | 1 | UK | 01.01.2021 17:10 | 01.01.2021 17:20 | 1 | 3 |
| 17 | 1 | UK | 01.01.2021 17:22 | 01.01.2021 17:55 | 1 | 3 |
| 54 | 1 | UK | 01.01.2021 18:15 | 01.01.2021 18:35 | 0 | 4 |
| 32 | 1 | UK | 01.01.2021 18:55 | 01.01.2021 19:35 | 0 | 5 |
You can't tell ROW_NUMBER to not be ROW_NUMBER. But you can use SUM() OVER () to count up cumulatively.
So, first make a flag for "this is the start of a new group" (more than 5mins since last row) and sum them up in order.
(I deleted the time diffs column, as it is over engineered and not required in its current form.)
WITH
diffs AS
(
select
session_id,
player_id,
country,
start_time,
end_time,
/* here is a new code with an expected result */
LAG(end_time)
OVER (
PARTITION BY player_id
ORDER BY start_time
)
AS prev_end_time
from
game_sessions
)
SELECT
*,
SUM(
IF(start_time < prev_end_time + INTERVAL '5' MINUTE, 0, 1)
)
OVER (
PARTITION BY player_id
ORDER BY start_time
)
AS expected_result
FROM
diffs
WHERE
1=1
AND player_id = 1
ORDER BY
player_id,
start_time
Demo: https://dbfiddle.uk/sJcWInTN
(Note, for future questions, please use ISO-8601 standard notation for dates and times, it makes inserting the data into a table much easier, reliable, etc.)