How would you call for the counts of nodes that fall into different ranges?

[geronimo4j]

Hi I have User and Rating nodes where users can leave ratings for different things. I'm interested in graphing the distribution of users' ratings, thinking because ratings can be > 0 and <=5 I can do it in 5 tiers:

0, 1, 2, 3, 4-5 stars

And essentially end up with something like:

0 - .999... = 14 ratings, average rating .58
1 - 1.999 = 11 ratings, average rating: 1.8
2 - 2.999 = 9 ratings, average rating: 2.2
etc.

Curious how you would set up a query to do this? One idea is to just have 5 separate calls where each looks for the count in the 5 ranges, is there another way to return the ratings for a user separated by range? And have some of this aggregate type info

Thank you in advance!

[giuseppe_villan]

@geronimo4j

You could return all ranges at once with this query:

UNWIND [[0,1], [1,2], [2,3], [3,4], [4,5]] as range // list of lists
MATCH (n:User)-->(r:Rating) // match rating
WHERE r.rating > range[0] and r.rating <= range[1]  // filter rating (from 0 to 1, from 1 to 2, etc.....)
RETURN range, count(r.rating), avg(r.rating) // return range, count and average

where I created a list of ranges, then I UNWIND it and after where I returned average and count.

With a dataset like this:

create (u1:User {id: 1})-[:HAS_RATE]->(:Rating {rating: 1}), (u1)-[:HAS_RATE]->(:Rating {rating: 1.5})
create (:User {id: 2})-[:HAS_RATE]->(:Rating {rating: 2.2})
create (:User {id: 3})-[:HAS_RATE]->(:Rating {rating: 2})
create (:User {id: 4})-[:HAS_RATE]->(:Rating {rating: 3.1})
create (:User {id: 5})-[:HAS_RATE]->(:Rating {rating: 4.4})
create (:User {id: 6})-[:HAS_RATE]->(:Rating {rating: 4.6})
create (:User {id: 7})-[:HAS_RATE]->(:Rating {rating: 5})
create (:User {id: 8})-[:HAS_RATE]->(:Rating {rating: 0.3})

it returns:

range	| count(r.rating)	    | avg(r.rating)
[0, 1]	| 2	                    | 0.65
[1, 2]	| 2	                    | 1.75
[2, 3]	| 1	                    | 2.2
[3, 4]	| 1	                    | 3.1
[4, 5]	| 3	                    | 4.666666666666667

[geronimo4j]

Wow this is really cool, so essentially you're calculating all of it one by one, I just read about UNWIND and it looks very good, have never used it before.

Thank you very much, looks like it does exactly what I'm after!

Happy and healthy holidays!