transfer_queries.cql

// Top Sellers
MATCH (t:Transfer)-[:FROM_CLUB]->(c) 
WHERE t.transfer_season = '12/13'
RETURN c.club_name_short, sum(t.transfer_fee) 
ORDER BY sum(t.transfer_fee) DESC
LIMIT 25

// Top Buyers
MATCH (t:Transfer)-[:TO_CLUB]->(c) 
WHERE t.transfer_season = '12/13'
RETURN c.club_name_short, sum(t.transfer_fee) 
ORDER BY sum(t.transfer_fee) DESC
LIMIT 25

// Balance
MATCH (acq:Transfer)-[:TO_CLUB]->(c)
WHERE acq.transfer_season = '14/15' 
WITH c, sum(acq.transfer_fee) AS spent
MATCH (c)<-[:FROM_CLUB]-(sale:Transfer) 
WHERE sale.transfer_season = '14/15'
RETURN c.club_name_short, spent, sum(sale.transfer_fee) AS received, spent - sum(sale.transfer_fee) as balance
ORDER BY balance DESC
LIMIT 25

// Balances over last 26 years
MATCH (acq:Transfer)-[:TO_CLUB]->(c:Club)
WITH c, sum(acq.transfer_fee) AS spent
MATCH (c)<-[:FROM_CLUB]-(sale:Transfer) 
RETURN c.club_name_short, spent, sum(sale.transfer_fee) AS received, spent - sum(sale.transfer_fee) as balance
ORDER BY balance DESC
LIMIT 100

// Largest lumps of money 
MATCH (buyer)-[f:CASH_FLOW]->(seller) return * ORDER BY f.total DESC limit 50

// Largest short term benefits/loses on individual players (as text)
MATCH (p:Player)<-[:OF_PLAYER]-(acq:Transfer)-[:TO_CLUB]->(c), (acq)-[:FROM_CLUB]->(boughtFrom),
 (p:Player)<-[:OF_PLAYER]-(sale:Transfer)-[:FROM_CLUB]->(c), (sale)-[:TO_CLUB]->(soldTo)
WHERE acq.transfer_year < sale.transfer_year AND NOT sale:Loan AND NOT acq:Loan
RETURN c.club_name + " bought " + p.player_name + " from " + boughtFrom.club_name + " in " + acq.transfer_year + " for " + acq.transfer_fee_text + " and sold it to " + soldTo.club_name + " " + (sale.transfer_year - acq.transfer_year) + " years later in " + sale.transfer_year + " for " + sale.transfer_fee_text + CASE WHEN (sale.transfer_fee - acq.transfer_fee)>=0 THEN " making a profit of " + ((sale.transfer_fee - acq.transfer_fee)/1000000) + "Mill. £" ELSE " losing " + (abs(sale.transfer_fee - acq.transfer_fee)/1000000) + "Mill. £" END as story
order by (sale.transfer_fee - acq.transfer_fee)/(sale.transfer_year - acq.transfer_year) desc
limit 10

// Top 3 biggest spenders per season
MATCH (acq:Transfer)-[:TO_CLUB]->(c)
WITH c, acq.transfer_year AS season, sum(acq.transfer_fee) AS spent
MATCH (c)<-[:FROM_CLUB]-(sale:Transfer) 
WHERE sale.transfer_year = season
WITH c.club_name_short as club, season, spent, sum(sale.transfer_fee) AS received, spent - sum(sale.transfer_fee) as balance
WITH season,club, balance
ORDER BY season, balance DESC
WITH season, COLLECT({club: club, balance: balance})[0..3] AS records
UNWIND records AS record 
RETURN season, record.club, record.balance
ORDER BY season DESC

// Length 3 cash flow paths for given team and season
MATCH path = (buyer:Club)-[:CASH_FLOW*..3]->(seller:Club) 
WHERE buyer.club_name = "Real Madrid" AND all(f in relationships(path) WHERE f.year=2000)
return *

// Club's transfer balance per season
MATCH (acq:Transfer)-[:TO_CLUB]->(c)
WHERE c.club_name = 'Real Madrid'
WITH c, acq.transfer_year AS season, sum(acq.transfer_fee) AS spent
MATCH (c)<-[:FROM_CLUB]-(sale:Transfer) 
WHERE sale.transfer_year = season
RETURN c.club_name_short, season, spent, sum(sale.transfer_fee) AS received, (spent - sum(sale.transfer_fee))/1000000 + "M" as balance
ORDER BY season
LIMIT 25

// Largest short term benefits/loses on individual players (as table)
MATCH (boughtFrom:Club)<-[:FROM_CLUB]-(acq:Transfer)-[:TO_CLUB]->(c)<-[:FROM_CLUB]-(sale:Transfer)-[:TO_CLUB]->(soldTo:Club), 
	 (acq:Transfer)-[:OF_PLAYER]->(p:Player)<-[:OF_PLAYER]-(sale:Transfer) 
WHERE acq.transfer_year < sale.transfer_year AND NOT sale:Loan AND NOT acq:Loan
RETURN c.club_name AS Club, p.player_name AS Player, ((sale.transfer_fee - acq.transfer_fee)/1000000) AS ProfitLoss, boughtFrom.club_name AS BoughtFrom, acq.transfer_year AS BoughtYear, acq.transfer_fee_text AS BuyPrice, soldTo.club_name AS SoldTo, sale.transfer_year AS SaleYear, sale.transfer_fee_text AS SalePrice
order by (sale.transfer_fee - acq.transfer_fee)/(sale.transfer_year - acq.transfer_year) desc
limit 10

//What is the most central club in the club network?
MATCH p=allShortestPaths((source:Club)-[*]-(target:Club))
WHERE id(source) < id(target) and length(p) > 1
UNWIND nodes(p)[1..-1] as n
RETURN n.club_name, count(*) as betweenness
ORDER BY betweenness DESC