version: '3.7'

services:
  neo4j:
    image: neo4j:4.0.0-enterprise
    container_name: "quickgraph-aus-open"
    volumes:
      - ./plugins:/plugins
      - ./data:/data
      - ./import:/import
    ports:
      - "7474:7474"
      - "7687:7687"
    environment:
      - "NEO4J_ACCEPT_LICENSE_AGREEMENT=yes"
      - "NEO4J_AUTH=neo4j/neo"
      - NEO4J_apoc_import_file_use__neo4j__config=true
      - NEO4J_apoc_import_file_enabled=true
      - NEO4JLABS_PLUGINS=["apoc"]

docker-compose up

Started quickgraph-aus-open ... done
Attaching to quickgraph-aus-open
quickgraph-aus-open | Changed password for user 'neo4j'.
quickgraph-aus-open | Fetching versions.json for Plugin 'apoc' from https://neo4j-contrib.github.io/neo4j-apoc-procedures/versions.json
quickgraph-aus-open | Installing Plugin 'apoc' from https://github.com/neo4j-contrib/neo4j-apoc-procedures/releases/download/4.0.0.0/apoc-4.0.0.0-all.jar to /plugins/apoc.jar
quickgraph-aus-open | Applying default values for plugin apoc to neo4j.conf
quickgraph-aus-open | Directories in use:
quickgraph-aus-open |   home:         /var/lib/neo4j
quickgraph-aus-open |   config:       /var/lib/neo4j/conf
quickgraph-aus-open |   logs:         /logs
quickgraph-aus-open |   plugins:      /plugins
quickgraph-aus-open |   import:       /import
quickgraph-aus-open |   data:         /var/lib/neo4j/data
quickgraph-aus-open |   certificates: /var/lib/neo4j/certificates
quickgraph-aus-open |   run:          /var/lib/neo4j/run
quickgraph-aus-open | Starting Neo4j.
quickgraph-aus-open | 2020-01-21 22:24:29.976+0000 INFO  ======== Neo4j 4.0.0 ========
quickgraph-aus-open | 2020-01-21 22:24:29.982+0000 INFO  Starting...
quickgraph-aus-open | 2020-01-21 22:24:35.656+0000 INFO  Called db.clearQueryCaches(): Query cache already empty.
quickgraph-aus-open | 2020-01-21 22:24:35.656+0000 INFO  Called db.clearQueryCaches(): Query cache already empty.
quickgraph-aus-open | 2020-01-21 22:24:35.656+0000 INFO  Called db.clearQueryCaches(): Query cache already empty.
quickgraph-aus-open | 2020-01-21 22:24:40.765+0000 INFO  Sending metrics to CSV file at /var/lib/neo4j/metrics
quickgraph-aus-open | 2020-01-21 22:24:40.790+0000 INFO  Bolt enabled on 0.0.0.0:7687.
quickgraph-aus-open | 2020-01-21 22:24:40.791+0000 INFO  Started.
quickgraph-aus-open | 2020-01-21 22:24:40.879+0000 INFO  Server thread metrics have been registered successfully
quickgraph-aus-open | 2020-01-21 22:24:41.723+0000 INFO  Remote interface available at http://0.0.0.0:7474/

LOAD CSV WITH HEADERS FROM 'https://raw.githubusercontent.com/JeffSackmann/tennis_wta/master/wta_matches_2019.csv' AS row
RETURN row
LIMIT 1;

:use system

CREATE DATABASE womens;
CREATE DATABASE mens;

SHOW DATABASES;

:use womens

CREATE CONSTRAINT ON (p:Player)
ASSERT p.id IS UNIQUE;

CREATE CONSTRAINT ON (m:Match)
ASSERT m.id IS UNIQUE;

CREATE CONSTRAINT ON (t:Tournament)
ASSERT (t.name, t.year) IS NODE KEY;

// Only keep Australian open matches
LOAD CSV WITH HEADERS FROM 'https://raw.githubusercontent.com/JeffSackmann/tennis_wta/master/wta_matches_2019.csv' AS row
WITH row, split(row.score, ' ') AS rawSets WHERE row.tourney_name = 'Australian Open'
WITH row, row.tourney_date + '_' + row.match_num AS matchId

// Create nodes for Tournaments, Matches, and Players
MERGE (t:Tournament {name: row.tourney_name, year: date(row.tourney_date).year})

MERGE (m:Match {id: matchId})
SET m.round = row.round, m.score = row.score

MERGE (p1:Player {id: row.winner_id})
SET p1.name = row.winner_name

MERGE (p2:Player {id: row.loser_id})
SET p2.name = row.loser_name

// Create relationships between nodes
MERGE (p1)-[:WINNER]->(m)
MERGE (p2)-[:LOSER]->(m)
MERGE (m)-[:IN_TOURNAMENT]->(t)

CALL apoc.periodic.iterate(
  "UNWIND range(2000, 2019) AS year RETURN year",
  "WITH 'https://raw.githubusercontent.com/JeffSackmann/tennis_wta/master/wta_matches_' AS base,
        year
   LOAD CSV WITH HEADERS FROM base + year + '.csv' AS row
   WITH row, split(row.score, ' ') AS rawSets WHERE row.tourney_name = 'Australian Open'
   WITH row, row.tourney_date + '_' + row.match_num AS matchId

   MERGE (t:Tournament {name: row.tourney_name, year: date(row.tourney_date).year})

   MERGE (m:Match {id: matchId})
   SET m.round = row.round, m.score = row.score

   MERGE (p1:Player {id: row.winner_id})
   SET p1.name = row.winner_name

   MERGE (p2:Player {id: row.loser_id})
   SET p2.name = row.loser_name

   MERGE (p1)-[:WINNER]->(m)
   MERGE (p2)-[:LOSER]->(m)
   MERGE (m)-[:IN_TOURNAMENT]->(t)
", {})

// Store the rounds in a list that will be used to sort matches
:params rounds: ["R128", "R64", "R32", "R16", "QF", "SF", "F"];

// Build a map from that list
WITH apoc.map.fromLists( $rounds, range(0, size($rounds)-1)) AS rounds

// Collect matches grouped by player and tournament, ordered by round
MATCH (t:Tournament)<-[:IN_TOURNAMENT]-(m:Match)<--(player)
WITH player, m, t
ORDER BY player, rounds[m.round]
WITH player, t, collect(m) AS matches
WHERE size(matches) > 1

// Add NEXT_MATCH relationship between adjacent matches
CALL apoc.nodes.link(matches, "NEXT_MATCH")
RETURN count(*);

// Collect tournaments ordered by year
MATCH (t:Tournament)
WITH t
ORDER BY t.year
WITH collect(t) AS tournaments

// Add NEXT_TOURNAMENT between adjacent matches
CALL apoc.nodes.link(tournaments, "NEXT_TOURNAMENT")
RETURN count(*);

MATCH (winner:Player)-[:WINNER]->(match:Match {round: "F"})<-[:LOSER]-(loser),
      (match)-[:IN_TOURNAMENT]->(tournament)
RETURN tournament.year AS year, winner.name AS winner,
       loser.name AS loser, match.score AS score
ORDER BY tournament.year

// Find all the matches that the winner of the tournament played
MATCH path = (p:Player)-[:WINNER]->(match:Match {round: "F"})<-[:NEXT_MATCH*]-(m)<-[:WINNER]-(p)

// Only get the winner of the 2019 tournament
// Only get the longest path of NEXT_MATCH relationships that includes all matches
// played by the winner
WHERE not((m)<-[:NEXT_MATCH]-()) AND (match)-[:IN_TOURNAMENT]-(:Tournament {year: 2019})

// Find the winners and losers of all the matches in which the winner participated
RETURN path,
       [node in nodes(path) WHERE node:Match | [p = (p1)-[:WINNER]->(node)<-[:LOSER]-(p2) | p]];

MATCH (player)-[:LOSER]->(:Match {round: "F"})-[:IN_TOURNAMENT]->(t)-[:NEXT_TOURNAMENT]->(t2),
      (player)-[:WINNER]->(:Match {round: "F"})-[:IN_TOURNAMENT]->(t2)
RETURN player.name AS player, t.year, t2.year

MATCH (player)-[:LOSER]->(:Match {round: "F"})-[:IN_TOURNAMENT]->(t)-[:NEXT_TOURNAMENT*]->(t2),
      (player)-[:WINNER]->(:Match {round: "F"})-[:IN_TOURNAMENT]->(t2)
RETURN player.name, t.year, t2.year

// Find the first year that a player lost the final
MATCH (player)-[:LOSER]->(:Match {round: "F"})-[:IN_TOURNAMENT]->(t)

// Where they haven't previously won the tournament
WHERE not((player)-[:WINNER]->(:Match {round: "F"})-[:IN_TOURNAMENT]->()-[:NEXT_TOURNAMENT*]->(t))

WITH player, t
ORDER BY player, t.year
WITH player, collect(t)[0] AS firstLoss

// Find the first year that a player won the final after that loss
MATCH (firstLoss)-[:NEXT_TOURNAMENT*]->(t2),
      (player)-[:WINNER]->(:Match {round: "F"})-[:IN_TOURNAMENT]->(t2)
      WITH player, firstLoss, t2
ORDER BY player, t2.year
WITH player, firstLoss, collect(t2)[0] AS firstWin

RETURN player.name, firstLoss.year, firstWin.year, firstWin.year - firstLoss.year AS theWait
ORDER BY theWait DESC

CALL apoc.periodic.iterate(
  "UNWIND range(2000, 2019) AS year RETURN year",
  "WITH 'https://raw.githubusercontent.com/JeffSackmann/tennis_wta/master/wta_matches_' AS base,
        year
   LOAD CSV WITH HEADERS FROM base + year + '.csv' AS row
   WITH row, split(row.score, ' ') AS rawSets WHERE row.tourney_name = 'Australian Open'
   WITH row, rawSets,
        [set in rawSets |
          apoc.text.regexGroups(set, \"(\\\\d{1,2})-(\\\\d{1,2})\")[0][1..]] AS sets,
        row.tourney_date + '_' + row.match_num AS matchId

   MATCH (m:Match {id: matchId})
   MATCH (p1:Player {id: row.winner_id})
   MATCH (p2:Player {id: row.loser_id})

   WITH m, sets, rawSets, matchId, p1, p2
   UNWIND range(0, size(sets)-1) AS setNumber
   MERGE (s:Set {id: matchId + '_' + setNumber})
   SET s.matchWinnerScore = toInteger(sets[setNumber][0]),
       s.matchLoserScore = toInteger(sets[setNumber][1]),
       s.score = rawSets[setNumber],
       s.number = setNumber +1
   MERGE (s)-[:IN_MATCH]->(m)
   FOREACH(ignoreMe IN CASE WHEN s.matchWinnerScore >= s.matchLoserScore THEN [1] ELSE [] END |
     MERGE (p1)-[:WINNER]->(s)
     MERGE (p2)-[:LOSER]->(s))
   FOREACH(ignoreMe IN CASE WHEN s.matchWinnerScore < s.matchLoserScore THEN [1] ELSE [] END |
     MERGE (p1)-[:LOSER]->(s)
     MERGE (p2)-[:WINNER]->(s))
", {});

MATCH (winner:Player)-[:WINNER]->(:Match {round: "F"})-[:IN_TOURNAMENT]->(t)
MATCH (winner)-[:WINNER]->(match)-[:IN_TOURNAMENT]->(t)
WITH winner, match, t
ORDER BY t.year
WITH winner, t,
     collect([(match)<-[:IN_MATCH]-(set:Set)
              WHERE (winner)-[:LOSER]->(set) | set
             ][0]) AS setDropped
WHERE size(setDropped) = 0
RETURN winner.name AS winner, t.year AS year

WITH apoc.map.fromLists( $rounds, range(0, size($rounds)-1)) AS rounds
MATCH (winner:Player)-[:WINNER]->(:Match {round: "F"})-[:IN_TOURNAMENT]->(t)
MATCH (winner)-[:WINNER]->(match)-[:IN_TOURNAMENT]->(t),
      (match)<-[:LOSER]-(opponent)
WHERE (winner)-[:LOSER]->(:Set)-[:IN_MATCH]->(match)

WITH *
ORDER BY rounds[match.round]

WITH winner, t,
     collect({round: match.round, opponent: opponent.name, score: match.score }) AS matches,
     collect([(match)<-[:IN_MATCH]-(set)<-[:LOSER]-(winner) | set]) AS sets
RETURN winner.name AS winner, t.year AS year, size(matches) AS count,
       size(apoc.coll.flatten(sets)) AS sets,  matches
ORDER BY count DESC
LIMIT 5