How to get max sequence of rows? [duplicate]

Question

This question already has answers here:

Select longest continuous sequence (2 answers)

Closed 9 years ago.

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