Cypher aggregate function on different nodes to evaluate condition for multiple nodes

user_8961826 0
Viewed 151

Metagraph of the database

I have a Neo4j database of Reddit over a specific time period.

I start from a specific subreddit, let's call it X. From X, I define the top_users of this subreddit as the users who have at least 1 post with a score higher than the average score of posts on this subreddit. Next, I want to look for the other subreddits where the top_users of subreddit X are also top_users but now based on the average score of those subreddits' posts and the top_users' posts on those subreddits.

This is what I already have, but not sure how to proceed. How should I best do this query?

MATCH (p:Post)-[:Submitted]->(:Subreddit {display_name: 'X'})
WITH avg(toFloat(p.score)) as score_avg
MATCH (u:User)-[:Submitted]->(p:Post)-[:Submitted]->(:Subreddit {display_name: 'X'})
WHERE p.score > score_avg
WITH DISTINCT u as top_users
MATCH (top_users)-[:Submitted]->(:Post)-[:Submitted]->(sub:Subreddit)
WITH sub as top_users_subs
MATCH (p:Post)-[:Submitted]->(top_users_subs)
WITH avg(toFloat(p.score)) as subs_score, top_users_subs
cypher neo4j
1 Answers

You need a subquery like apoc.cypher.run to not mess with your cardinality.

MATCH (p:Post)-[:Submitted]->(:Subreddit {display_name: 'X'})
WITH avg(toFloat(p.score)) as score_avg
MATCH (u:User)-[:Submitted]->(p:Post)-[:Submitted]->(:Subreddit {display_name: 'X'})
WHERE p.score > score_avg
WITH DISTINCT u as top_users
MATCH (top_users)-[:Submitted]->(:Post)-[:Submitted]->(sub:Subreddit)
// You need to run a subquery to get the average score for each subreddit
CALL apoc.cypher.run("MATCH (p:Post)-[:Submitted]->(s:Subreddit) WHERE s.display_name = $reddit RETURN avg(toFloat(p.score)) as score_avg",{reddit:sub.display_name}) yield value
// now we have top users and we have average score
// You can do whatever you want to now...
user_66928950
Related