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%).
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 ...
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.