Consecutive list seperated by x - consecutive Olympic games

Neo4j Graph Platform
,
1

I am trying to build a query that returns a list of consecutive Olympic games gold medalists. the consecutive part is what I'm struggling with. I'm not sure how to build a list where only dates separated by 4 years are counted to make a win streak.
e.g. this list jumps from 1936 to 1948.
[1932, 1936, 1948, 1948, 1952, 1956, 1960]
so i want
[1932, 1936]
and
[1948, 1952, 1956, 1960]

this is my current query

||    MATCH (a:Player)-[r:COMPETED]->(e:sport)-[r2:COMPETED_AT]->(g:Olympic)|
|---|---|
||    WHERE  r.medal = Gold|
||    ORDER BY g.year, a.name ASC|
||    WITH a, collect(r.medal) as results, collect(g.year) as years|
||    CALL apoc.coll.split(results, false) YIELD value as winStreak|
||    WITH a, max(size(winStreak)) as longestStreak, results, years|
||    RETURN a.name as teamName, longestStreak, results, years|
||    ORDER BY longestStreak DESC|
||limit 100|

This works to create a list like the one bellow but im not sure how to only return consectuive int seperated by 4 :
"Aladr Gerevich (-Gerei)" 7 ["Gold", "Gold", "Gold", "Gold", "Gold", "Gold", "Gold"] [1932, 1936, 1948, 1948, 1952, 1956, 1960]

please note i am using int not year as my dates go back before 1900.

2

Hi @tempest8032 and welcome to the Neo4j forum :slight_smile:

I don't have a dataset to test but if you add null values by using OPTIONAL MATCH, I think you can achieve what you are looking for, like this the apoc.coll.split() will split the streak (you can use coalese to replace null by false).

MATCH (a:Player)-[r:COMPETED]->(e:sport)
OPTIONAL MATCH (e)-[r2:COMPETED_AT]->(g:Olympic)
...

Best regards,
Cobra

3

Thank you for the try Cobra but no luck, just the same results.
i think we can ignore the dataset and just take the list [1932, 1936, 1948, 1948, 1952, 1956, 1960]. how would does someone get the longest consecutive set from a list e.g. [1948, 1952, 1956, 1960] .
if i can find a good answer to that i think i can build the rest.

4 
WITH [1932, 1936, 1948, 1948, 1952, 1956, 1960] AS array
RETURN array, REDUCE(
    r = [array[0]], 
    i IN RANGE(1, size(array)-1) | 
    CASE WHEN array[i] = array[i-1] +4
      THEN r[0..-1] + (r[-1] + '-' + array[i-1]) + TOSTRING(array[i])
      ELSE r END
) AS ranges

The above code almost works but the output isnt quite right.
im getting : ["1932-1932", "1936-1948", "1952-1952", "1956-1956", "1960"]
instead of ["1932-1936", "1948-1960"]

still tinkering with it but i feel like i might be on the right path.

5

Sorry, I have to disagree with you, I think it can be solve by playing with the Cypher query, I created a little dataset based on your data model and I wrote this query:

MATCH (player:Player)
MATCH (olympic:Olympic)
OPTIONAL MATCH (player)-[r:COMPETED]->()-[r2:COMPETED_AT]->(olympic)
WITH 
    player, 
    CASE r.medal WHEN "Gold" THEN {r: r, year: olympic.year} ELSE false END AS result
ORDER BY olympic.year ASC
WITH 
    player, 
    collect(result) AS results
CALL apoc.coll.split(results, false) 
YIELD value AS winStreak
RETURN 
    player.name AS name, 
    max(size(winStreak)) AS longestStreak, 
    [i IN winStreak | i.r.medal] AS winStreak, 
    [i IN winStreak | i.year] AS years;

The result is:

image
image1254×167 3.72 KB

Best regards,
Cobra