Neo4J Group by and ranking

[dhlakshmikanth]

I am working on sample data, creating recommendation with respective to similar materials bought. its working fine
MATCH (eid:eid{id:'102'})-[:BOUGHT]->(mat:material)<-[:BOUGHT]-(someOthereid:eid)-[:BOUGHT]->(m2:material)
WHERE
NOT((eid)-[:BOUGHT]->(m2))
AND eid <> someOthereid
RETURN distinct m2,eid

I am trying to add rank and group by region by vendor to this query. Please suggest

[koji]

I have two questions.

Does the schema consist of the following?

(:eid)-[:BOUGHT]->(:material)-[:FROM]->(:vendor)

and the region is included in the vendor, as shown below?

(:vendor {region:'someregion'})

[dhlakshmikanth]

yes @koji schema exists and region included in vendor

I got this query, gives you some idea about what I am looking for but I am unable to get rank by vendor by region with respect to material bought

MATCH (eid:eid)-[:BOUGHT]->(:material)<-[:BOUGHT]-(otherEid:eid)
WHERE eid.id = '104' AND eid <> otherEid
WITH DISTINCT eid, otherEid
MATCH (otherEid)-[:BOUGHT]->(m:material)
WHERE NOT (eid)-[:BOUGHT]->(m)
WITH eid, m, COUNT(*) AS timessold
MATCH (n:bookdb), (ecl:ecountry), (ven:vendor), (vloc:vlocation) 
WHERE n.mat = m.id AND ecl.id = n.eloc AND ven.id = n.ven AND vloc.id = n.vloc
RETURN eid, m, timessold, ecl, ven, vloc
ORDER BY timessold DESC, m

[koji]

Hi @dhlakshmikanth

Sorry for the late reply.
I created the sample data like this.

CREATE (eid101:eid {id: '101'}),
       (eid102:eid {id: '102'}),
       (eid103:eid {id: '103'}),
       (eid104:eid {id: '104'})
CREATE (m01:material {id: 'm01', name: 'material01'}),
       (m02:material {id: 'm02', name: 'material02'}),
       (m03:material {id: 'm03', name: 'material03'}),
       (m04:material {id: 'm04', name: 'material04'}),
       (m05:material {id: 'm05', name: 'material05'})
CREATE (b01:bookdb {mat: 'm01', eloc: 'eloc01', ven: 'ven01', vloc: 'vloc01'}),
       (b02:bookdb {mat: 'm02', eloc: 'eloc01', ven: 'ven01', vloc: 'vloc01'}),
       (b03:bookdb {mat: 'm03', eloc: 'eloc01', ven: 'ven02', vloc: 'vloc01'}),
       (b04:bookdb {mat: 'm04', eloc: 'eloc02', ven: 'ven02', vloc: 'vloc01'}),
       (b05:bookdb {mat: 'm05', eloc: 'eloc02', ven: 'ven03', vloc: 'vloc01'}),
       (b06:bookdb {mat: 'm05', eloc: 'eloc02', ven: 'ven03', vloc: 'vloc02'})
CREATE (:ecountry {id: 'eloc01', name: 'eloc01name'}),
       (:ecountry {id: 'eloc02', name: 'eloc02name'})
CREATE (:vendor {id: 'ven01', name: 'ven01name'}),
       (:vendor {id: 'ven02', name: 'ven02name'}),
       (:vendor {id: 'ven03', name: 'ven03name'})
CREATE (:vlocation {id: 'vloc01', name: 'vloc01name'}),
       (:vlocation {id: 'vloc02', name: 'vloc02name'})
CREATE (eid101)-[:BOUGHT]->(m03),
       (eid102)-[:BOUGHT]->(m01),
       (eid102)-[:BOUGHT]->(m02),
       (eid102)-[:BOUGHT]->(m03),
       (eid102)-[:BOUGHT]->(m04),
       (eid102)-[:BOUGHT]->(m05),
       (eid103)-[:BOUGHT]->(m01),
       (eid103)-[:BOUGHT]->(m01),
       (eid103)-[:BOUGHT]->(m02),
       (eid103)-[:BOUGHT]->(m03),
       (eid104)-[:BOUGHT]->(m01),
       (eid104)-[:BOUGHT]->(m02)

And I changed the Return part of your code a bit for the list format.

MATCH (eid:eid)-[:BOUGHT]->(:material)<-[:BOUGHT]-(otherEid:eid)
  WHERE eid.id = '104' AND eid <> otherEid
WITH DISTINCT eid, otherEid
MATCH (otherEid)-[:BOUGHT]->(m:material)
  WHERE NOT (eid)-[:BOUGHT]->(m)
WITH eid, m, COUNT(*) AS timessold
MATCH (n:bookdb), (ecl:ecountry), (ven:vendor), (vloc:vlocation)
  WHERE n.mat = m.id AND ecl.id = n.eloc AND ven.id = n.ven AND vloc.id = n.vloc
RETURN eid.id, m.id, m.name, timessold, collect(DISTINCT(ecl.name)), collect(DISTINCT(ven.name)),
       collect(DISTINCT(vloc.name))
  ORDER BY timessold DESC, m.id

I think the answer is correct.