Head's Up! Site migration is underway. Phase 2: migrate recent content
β09-05-2021 03:55 AM
I've a graph of students and the various books that they've read. I want to find out the top 10% of students who've read the most books. How can I do that? I've tried the following cypher syntax:
'''
MATCH (s:Student)-[:READ]->(b:Book)
WITH s, COUNT(b) AS no_of_books
WHERE no_of_books > percentileCont(no_of_books, 0.9)
RETURN s.Name, no_of_books
'''
The error 'invalid use of aggregating function' is returned. It seems that trying to use two aggregating functions on top of each other is an issue here. How can I tweak my syntax to make it work?
I'll be happy to use the LIMIT function instead if it can work with percentages as well.
Solved! Go to Solution.
β09-06-2021 01:32 AM
Hi!
Maybe:
with 50 as per
MATCH(:Student)
with toInteger(floor(count(*) * per / 100)) as lim
call apoc.cypher.run(
'MATCH (s:Student)-[:READ]->(b:Book)
WITH s, COUNT(b) AS no_of_book
return s
order by no_of_book DESC limit $limit'
, {limit : lim}) yield value
return value
Bennu
β09-05-2021 09:39 PM
Try this:
MATCH (s:Student)-[:READ]->(b:Book)
WITH s.name as name, COUNT(distinct b) AS no_of_books
RETURN name. no_of_books order by no_of_books desc limit 10
β09-05-2021 10:05 PM
That would give me the top 10 students, rather than the top 10 percent of students?
β09-05-2021 11:03 PM
Okay. Try this:
MATCH (b:Book)
WITH b, count(distinct b) as totBooks
MATCH (s:Student)-[:READ]->(b)
WITH s.name as name, COUNT(distinct b) AS no_of_books, totBooks
WITH name, ((no_of_books * 100) / totBooks) as prcnt
RETURN name. prcnt order by prcnt desc limit 10
β09-06-2021 01:32 AM
Hi!
Maybe:
with 50 as per
MATCH(:Student)
with toInteger(floor(count(*) * per / 100)) as lim
call apoc.cypher.run(
'MATCH (s:Student)-[:READ]->(b:Book)
WITH s, COUNT(b) AS no_of_book
return s
order by no_of_book DESC limit $limit'
, {limit : lim}) yield value
return value
Bennu
β09-07-2021 06:34 AM
Another possible solution without using the APOC plug-in
MATCH (s:Student)-[:READ]->(b:Book)
WITH s, COUNT(b) AS no_of_books
ORDER BY no_of_books DESC
WITH COLLECT ({Student_Name: s.Name, No_of_Books: no_of_books}) AS books_per_stu
WITH books_per_stu, toInteger(size(books_per_stu)/100) AS percentile
UNWIND book_per_stu[0..percentile] AS top_stu
RETURN top_stu
Seems like, surprisingly, there's no straightforward way to do this, like my pseudo-code in my first post. The above syntax will return the results as a list of dictionaries rather than in a tabular format, but then again, none of the other solutions proposed here is able to return results in tabular format either.
All the sessions of the conference are now available online