I have a the following result from a SELECT query with ORDER BY player_id ASC, time ASC in PostgreSQL database:
player_id points time 395 0 2018-06-01 17:55:23.982413-04 395 100 2018-06-30 11:05:21.8679-04 395 0 2018-07-15 21:56:25.420837-04 395 100 2018-07-28 19:47:13.84652-04 395 0 2018-11-27 17:09:59.384-05 395 100 2018-12-02 08:56:06.83033-05 399 0 2018-05-15 15:28:22.782945-04 399 100 2018-06-10 12:11:18.041521-04 454 0 2018-07-10 18:53:24.236363-04 675 0 2018-08-07 20:59:15.510936-04 696 0 2018-08-07 19:09:07.126876-04 756 100 2018-08-15 08:21:11.300871-04 756 100 2018-08-15 16:43:08.698862-04 756 0 2018-08-15 17:22:49.755721-04 756 100 2018-10-07 15:30:49.27374-04 756 0 2018-10-07 15:35:00.975252-04 756 0 2018-11-27 19:04:06.456982-05 756 100 2018-12-02 19:24:20.880022-05 756 100 2018-12-04 19:57:48.961111-05
I’m trying to find each player’s longest streak where points = 100, with the tiebreaker being whichever streak began most recently. I also need to determine the time at which that player’s longest streak began. The expected result would be:
player_id longest_streak time_began 395 1 2018-12-02 08:56:06.83033-05 399 1 2018-06-10 12:11:18.041521-04 756 2 2018-12-02 19:24:20.880022-05
Advertisement
Answer
A gaps-and-islands problem indeed.
Assuming:
- “Streaks” are not interrupted by rows from other players.
- All columns are defined
NOT NULL. (Else you have to do more.)
This should be simplest and fastest as it only needs two fast row_number() window functions:
SELECT DISTINCT ON (player_id)
player_id, count(*) AS seq_len, min(ts) AS time_began
FROM (
SELECT player_id, points, ts
, row_number() OVER (PARTITION BY player_id ORDER BY ts)
- row_number() OVER (PARTITION BY player_id, points ORDER BY ts) AS grp
FROM tbl
) sub
WHERE points = 100
GROUP BY player_id, grp -- omit "points" after WHERE points = 100
ORDER BY player_id, seq_len DESC, time_began DESC;
db<>fiddle here
Using the column name ts instead of time, which is a reserved word in standard SQL. It’s allowed in Postgres, but with limitations and it’s still a bad idea to use it as identifier.
The “trick” is to subtract row numbers so that consecutive rows fall in the same group (grp) per (player_id, points). Then filter the ones with 100 points, aggregate per group and return only the longest, most recent result per player.
Basic explanation for the technique:
We can use GROUP BY and DISTINCT ON in the same SELECT, GROUP BY is applied before DISTINCT ON. Consider the sequence of events in a SELECT query:
About DISTINCT ON: