Return 'biggest fan of a tag' neo4j

Question

I want to match blogs that have most like interaction with certain tags (in the example I used dog) but posted in it less than 10 times.

How can I achieve that?

Currently, I get this error message

Invalid use of aggregating function count(...) in this context (line 6, column 7 (offset: 210))
"WHERE COUNT(posted) < 10"

Query

MATCH (b:Blog)-[r:liked]-(p:Post)-[:tagged]-(t:Tag)
WHERE t.content = "dog"
WITH b, COUNT(r) as interaction_count

MATCH (b:Blog)-[posted:posted_by]-(:Post)-[:tagged]-(t:Tag)
WHERE COUNT(posted) < 10

RETURN b, interaction_count
ORDER BY interaction_count DESC
LIMIT 10

Answer 1

The error is here:

WHERE COUNT(posted) < 10

You need to use these aggregations in a WITH clause as you did with your earlier count in the query.

Change the query to this and it should work:

MATCH (b:Blog)-[r:liked]-(p:Post)-[:tagged]-(t:Tag)
WHERE t.content = "dog"
WITH b, COUNT(r) as interaction_count

MATCH (b:Blog)-[posted:posted_by]-(:Post)-[:tagged]-(t:Tag)

WITH b,interaction_count, COUNT(posted) as p
WHERE p<10 

RETURN b, interaction_count
ORDER BY interaction_count DESC
LIMIT 10

The only difference is that I broke your "WHERE" into a "WITH/WHERE" pattern