Average with a if clause

[costa0096]

Hello community
I have this graph:

Blue Label is an Entity, and Orange label another entity. CONTRATO has the informations about a contract that both entities have made with each other
I have this query that basically calculates the average of the summed distance between the entities:

Match (e1:EntAdjudicante) -[C:CONTRATO]->(e2:EntAdjudicataria)
WITH point({ longitude: C.lonEante, latitude: C.latEante }) AS GeoEante, point({ longitude: C.lonEataria, latitude: C.latEataria }) AS GeoEataria
return avg(round(distance(GeoEante,GeoEataria))) AS EntitiesAVGDistance

Now i want to do this if clause: if the distance between entities is greater than the average, then i want to return that relationships marked differently than the others (in red colour for example)

I've made this query:

MATCH (e1:EntAdjudicante) -[C:CONTRATO]->(e2:EntAdjudicataria)
WITH point({ longitude: C.lonEante, latitude: C.latEante }) AS GeoEante, point({ longitude: C.lonEataria, latitude: C.latEataria }) AS GeoEataria 
WITH distance(GeoEante,GeoEataria) AS DistanceBetweenEntities 
WITH avg(round(DistanceBetweenEntities)) AS EntitiesAVGDistance
WHERE DistanceBetweenEntities > EntitiesAVGDistance
RETURN e1,e2

But three things:
1- I get the error: In a WITH/RETURN with DISTINCT or an aggregation, it is not possible to access variables declared before the WITH/RETURN: DistanceBetweenEntities (line 5, column 7 (offset: 343))
"WHERE DistanceBetweenEntities > EntitiesAVGDistance"
2- I don't know if it's possible to set a relationship the same way i would do it to a label
3- What do i have to return to have the graph and not only the labels?
Thank you all and stay safe!

[andrew_bowman · Accepted solution]

WITH clauses also restrict what is in scope. If a variable isn't included in a WITH clause, then it goes out of scope and is no longer accessible afterward.

Also, when aggregating, the non-aggregating variables become the grouping key, so go avoid including e1 and e2 as the grouping key, these need to be aggregated (along with their distance) at the same time that you calculate the average, and then after you have the average, you can apply your filter and UNWIND the aggregated values and project.

MATCH (e1:EntAdjudicante) -[C:CONTRATO]->(e2:EntAdjudicataria)
WITH e1, e2, point({ longitude: C.lonEante, latitude: C.latEante }) AS GeoEante, point({ longitude: C.lonEataria, latitude: C.latEataria }) AS GeoEataria 
WITH e1, e2, distance(GeoEante,GeoEataria) AS DistanceBetweenEntities 
WITH collect([e1, e2, DistanceBetweenEntities]) as distanceRecords, avg(round(DistanceBetweenEntities)) AS EntitiesAVGDistance
WITH [record IN distanceRecords WHERE record[2] > EntitiesAVGDistance] as distanceRecords
UNWIND distanceRecords as record
RETURN record[0] as e1, record[1] as e2, record[2] as distance

You can do the last WITH and UNWIND as a single UNWIND, applying the filter at the same time, but it reads a bit better when they're separated.

[costa0096]

andrew.bowman:

MATCH (e1:EntAdjudicante) -[C:CONTRATO]->(e2:EntAdjudicataria)
WITH e1, e2, point({ longitude: C.lonEante, latitude: C.latEante }) AS GeoEante, point({ longitude: C.lonEataria, latitude: C.latEataria }) AS GeoEataria 
WITH e1, e2, distance(GeoEante,GeoEataria) AS DistanceBetweenEntities 
WITH collect([e1, e2, DistanceBetweenEntities]) as distanceRecords, avg(round(DistanceBetweenEntities)) AS EntitiesAVGDistance
WITH [record IN distanceRecords WHERE record[2] > EntitiesAVGDistance] as distanceRecords
UNWIND distanceRecords as record
RETURN record[0] as e1, record[1] as e2, record[2] as distance

Nice friend, it worked
In resumen, i can have multiple WITH clauses and in each one i have to "go back" and "collect" all the variables i've defined previously, is that right?
Thank you for your answer, worked perfectly!
Greetings!

[andrew_bowman]

costa0096:

In resumen, i can have multiple WITH clauses and in each one i have to "go back" and "collect" all the variables i've defined previously, is that right?

Only if you want to keep working with those variables later.

Glad to help!

[costa0096]

That's amazing. I didn't knew that this was possible. Nice!
Stay safe friend