Search code examples
sqlpostgresqlgreatest-n-per-groupwindow-functionsgaps-and-islands

Find the longest streak of perfect scores per player

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
Solution

  • A 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: