How to get max sequence of rows?

Question

I have a table with statistic of games which has next rows (I only write about rows that has interest in this context):

id        BIGINT PRIMARY KEY
date      TIMESTAMP WITH TIME ZONE
status    VARCHAR  -- 'WON' or 'LOSE'
player_id BIGINT   -- FOREIGN KEY

I need to write a query that select number of consecutive win games (status = 'WON') by some user (user_id = %some id%).

Answer 1

A case for window functions:

SELECT count(*) As longest_streak
FROM  (
   SELECT row_number() OVER w
          - count(status = 'WON' OR NULL) OVER w AS grp
   FROM   game
   WHERE  player_id = 1          -- %some id%
   WINDOW w AS (ORDER BY date)
   ) sub
GROUP  BY grp
ORDER  BY count(*) DESC
LIMIT  1;

Or use the aggregate FILTER clause in Postgres 9.4 or later:

... 
   SELECT row_number() OVER w
          - count(*) FILTER (WHERE status = 'WON') OVER w AS grp
...

• Return counts for multiple ranges in a single SELECT statement

We count rows ordered by date and subtract the running number of wins. If the next row is a win, it still falls in the same group grp. Then get the maximum count per group.

Detailed explanation:

• Select longest continuous sequence