cancel
Showing results forΒ
Did you mean:Β

Node

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:

'''
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.

1 ACCEPTED SOLUTION
Graph Fellow

Hi!

Maybe:

``````with 50 as per
MATCH(:Student)
with toInteger(floor(count(*) * per / 100)) as lim
call apoc.cypher.run(
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

5 REPLIES 5
Graph Maven
``````Try this:

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
``````

That would give me the top 10 students, rather than the top 10 percent of students?

Graph Maven
``````Okay. Try this:

MATCH (b:Book)
WITH b, count(distinct b) as totBooks
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

``````
Graph Fellow

Hi!

Maybe:

``````with 50 as per
MATCH(:Student)
with toInteger(floor(count(*) * per / 100)) as lim
call apoc.cypher.run(
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

Node

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