# Query help - newbie - calculating rank order

(Rmasrani) #1

Hello Community.

I need some help to build the following query. Wondering what I am doing wrong.

I have a relationship between (a) and (b) as follows: (a)-[u:USES]->(b). I also have a property in u that specifies the number of times a has used b (u.count). So now I have information about how many times each of 8000 users have used any of 10,000 programs. So far so good.

Now I want to identify super users as the top in each relationship. I have the following already calculated: u.totalUsage - total number of times a has used b; u.PCTusage - percent value for the a-b link. All I need to identify the super users is a ranking in each a/b pair by u.PCTusage.

The following query appears to be calculating the rank over ALL a/b relationships, not specifically to each one. please be kind. I am really new to Neo

MATCH (t0:b)
WITH collect(t0) as allB
UNWIND allB as oneB
MATCH(u:USER)-[r:USES]->(t:PROG{label: oneB.label})
with r order by r.dc_sum_PCT desc
with collect(r) as allRels
FOREACH(i IN RANGE(0, SIZE(allRels)-1) |
FOREACH(x IN [allRels[i]] | SET x.rank = i));

Thank you

(Michael Hunger) #2

You need to group by user:

``````MATCH (oneB:b)
MATCH(u:USER)-[r:USES]->(t:PROG{label: oneB.label})
// group by user
// collect pair of use + program into a list
WITH u, sum(r.uses) as total, collect({uses: r.uses, program: t}) as uses
RETURN u, total, [pair in uses | {pct: toFloat(pair.uses) / total, program: t.label}] as uses
ORDER BY total DESC LIMIT 100
``````

(Rmasrani) #3

Thanks Michael. Works perfectly. Appreciate the help.