Return top percentage of results

[wanderingcatto]

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.

[ameyasoft]

wanderingcatto:

MATCH (s:Student)-[:READ]->(b:Book)
WITH s, COUNT(b) AS no_of_books

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

[wanderingcatto]

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

[ameyasoft]

ameyasoft:

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

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

[Bennu · Accepted solution]

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

[wanderingcatto]

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.