Find the maximum number of consecutive games in which a player is scoring

Hi

I have the following MySQL table

game_id, player_id, goals_count
1	1		1
2	1		0
3	1		1
4	1		2
5	1		2
6	1		1
7	1		0
8	1		1
1	2		0
12	2		1
13	2		1
14	2		1
15	2		1
16	2		1

I would like to find the MAX number of consecutive games where goal_count>0

In the above example, player#1 has scored 6 goals in 4 games (game id is 3,4,5,6)
and the other player #2 scored 5 goals in 5 consecutive games (game id is 12,13,14,15,16)

So the result can be something like the following:

player_id	max_games 	goalstotl
1		4		6
2		5		5

[code]CREATE TABLE games
( game_id SMALLINT NOT NULL PRIMARY KEY
, player_id SMALLINT
, goals_count SMALLINT
);
INSERT INTO games
VALUES
( 1 , 1 , 1 )
,( 2 , 1 , 0 )
,( 3 , 1 , 1 )
,( 4 , 1 , 2 )
,( 5 , 1 , 2 )
,( 6 , 1 , 1 )
,( 7 , 1 , 0 )
,( 8 , 1 , 1 )
,( 9 , 2 , 0 )
,( 10 , 2 , 0 )
,( 11 , 2 , 0 )
,( 12 , 2 , 1 )
,( 13 , 2 , 1 )
,( 14 , 2 , 1 )
,( 15 , 2 , 1 )
,( 16 , 2 , 1 )
;
CREATE VIEW streaks
AS
SELECT lo.player_id
, lo.game_id AS lo
, COUNT(*) AS streak
, hi.game_id AS hi
FROM games AS lo
INNER
JOIN games AS hi
ON hi.player_id = lo.player_id
AND hi.game_id >= lo.game_id
AND hi.goals_count > 0
INNER
JOIN games AS t3
ON t3.player_id = lo.player_id
AND t3.game_id BETWEEN lo.game_id AND hi.game_id
AND t3.goals_count > 0
WHERE lo.goals_count > 0
AND NOT EXISTS
( SELECT *
FROM games AS t4
WHERE t4.player_id = lo.player_id
AND t4.game_id BETWEEN lo.game_id and hi.game_id
AND t4.goals_count = 0 )
AND NOT EXISTS
( SELECT *
FROM games AS t5
WHERE t5.player_id = lo.player_id
AND t5.game_id =
( SELECT MAX(t6.game_id)
FROM games AS t6
WHERE t6.player_id = lo.player_id
AND t6.game_id < lo.game_id )
AND t5.goals_count > 0 )
AND NOT EXISTS
( SELECT *
FROM games AS t7
WHERE t7.player_id = lo.player_id
AND t7.game_id =
( SELECT MIN(t8.game_id)
FROM games AS t8
WHERE t8.player_id = lo.player_id
AND t8.game_id > hi.game_id )
AND t7.goals_count > 0 )
GROUP
BY lo.player_id
, lo.game_id
, hi.game_id
;
SELECT * FROM streaks
;

/*
player_id lo streak hi
1 1 1 1
1 3 4 6
1 8 1 8
2 12 5 16
*/

SELECT s.player_id
, s.lo
, s.streak
, s.hi
FROM ( SELECT player_id
, MAX(streak) AS longest
FROM streaks
GROUP
BY player_id ) AS m
INNER
JOIN streaks AS s
ON s.player_id = m.player_id
AND s.streak = m.longest
;
/*
player_id lo streak hi
1 3 4 6
2 12 5 16
*/[/code]

as you can see, the above solution identifies the longest streaks for each player

only a slight modification is required to sum op the goals scores… can you do it?

Wow, I thought it would be much easier to write such query. Anyway, thanks for your great effort.

Yes I can do it. Thanks again

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.