Neo4j List Consecutive

Question

I am currently working with a football match data set and trying to get Cypher to return the teams with the most consecutive wins.

At the moment I have a collect statement which creates a list i.e. [0,1,1,0,1,1,1] where '0' represents a loss and '1' represents a win. I am trying to return the team with the most consecutive wins.

Here is what my code looks like at the moment:

MATCH(t1:TEAM)-[p:PLAYS]->(t2:TEAM)
WITH [t1,t2] AS teams, p AS matches
ORDER BY matches.time ASC
UNWIND teams AS team
WITH team.name AS teamName, collect(case when ((team = startnode(matches)) AND (matches.score1 > matches.score2)) OR ((team = endnode(matches)) AND (matches.score2 > matches.score1)) then +1 else 0 end) AS consecutive_wins
RETURN teamName, consecutive_wins

This returns a list for each team showing their win / lose record in the form explained above (i.e. [0,1,0,1,1,0])

Any guidance or help in regards to calculating consecutive wins would be much appreciated.

Thanks

Answer 1

I answered a similar question here.

The key is using apoc.coll.split() from APOC Procedures, splitting on 0, which will yield a row per winning streak (list of consecutive 1's) as value. The size of each of the lists is the number of consecutive wins for that streak, so just get the max size:

// your query above
CALL apoc.coll.split(consecutive_wins, 0) YIELD value
WITH teamName, max(size(value)) as consecutiveWins
ORDER BY consecutiveWins DESC
LIMIT 1
RETURN teamName, consecutiveWins

Answer 2

Your use case does not actually require the detection of the most consecutive 1s (and it also does not need to use UNWIND).

The following query uses REDUCE to directly calculate the maximum number of consecutive wins for each team (consW keeps track of the current number of consecutive wins, and maxW is the maximum number of consecutive wins found thus far):

MATCH (team:TEAM)-[p:PLAYS]-(:TEAM)
WITH team, p
ORDER BY p.time ASC
WITH team,
  REDUCE(s = {consW: 0, maxW: 0}, m IN COLLECT(p) |
    CASE WHEN (team = startnode(m) AND (m.score1 > m.score2)) OR (team = endnode(m) AND (m.score2 > m.score1))
      THEN {consW: s.consW+1, maxW: CASE WHEN s.consW+1 > s.maxW THEN s.consW+1 ELSE s.maxW END}
      ELSE s
    END).maxW AS most_consecutive_wins
RETURN team.name AS teamName, most_consecutive_wins;