title: Neo4j - Can't use aggregate functions inside of aggregate functions
body: <p>everyone! I have a Neo4j database which contains information about visitors who visit a number of checkpoints at specific moments of time. visitors, visits and checkpoints are represented as nodes. </p>
  
  <p>Each visitor is identified by his/her device MAC address, each checkpoint has a unique id. </p>
  
  <p>Each visit node contains an EnterTime property, which is the time (num of millisec since 1970) when the visitor with some specific MAC address is first noticed near the respective checkpoint.</p>
  
  <p>Note: the duration of a visit should be calculated as the difference between EnterTime of the next visit and the EnterTime of this visit. </p>
  
  <p>My goal is to find some statistics about the visits, for example, 
  1) the average time of a visit by a specific visitor,
  2) the average time of a visit to a specific store,
  3) the most popular store (i. e. the store with the longest TOTAL time of visits
  4) the most popular store (i. e. the store with the longest AVERAGE time of visits) etc. </p>
  
  <p>The database is created with this cypher query:  </p>
  
  <pre><code>//Create visitors:
  CREATE (Visitor1: Visitor {MAC:'00:0a:95:9d:68:16'})
  CREATE (Visitor2: Visitor {MAC:'00:0a:95:9d:68:17'})
  CREATE (Visitor3: Visitor {MAC:'00:0a:95:9d:68:18'})
  
  //Create CheckPoints:
  CREATE (CheckPoint1: CheckPoint {CheckPointId: 1})
  CREATE (CheckPoint2: CheckPoint {CheckPointId: 2})
  CREATE (CheckPoint3: CheckPoint {CheckPointId: 3})
  CREATE (CheckPoint4: CheckPoint {CheckPointId: 4})
  CREATE (CheckPoint5: CheckPoint {CheckPointId: 5})
  
  
  //Create visits:
  //by visitor 1:
  CREATE  (Visitor1)-[:MAKES]-&gt;(Visit1: Visit {EnterTime: 1488358800000})-[:TO]-&gt;(CheckPoint1)
  CREATE  (Visitor1)-[:MAKES]-&gt;(Visit2: Visit {EnterTime: 1488359400000})-[:TO]-&gt;(CheckPoint2)
  CREATE  (Visitor1)-[:MAKES]-&gt;(Visit3: Visit {EnterTime: 1488361200000})-[:TO]-&gt;(CheckPoint3)
  CREATE  (Visitor1)-[:MAKES]-&gt;(Visit4: Visit {EnterTime: 1488363600000})-[:TO]-&gt;(CheckPoint4)
  CREATE  (Visitor1)-[:MAKES]-&gt;(Visit5: Visit {EnterTime: 1488364800000})-[:TO]-&gt;(CheckPoint5)
  CREATE  (Visitor1)-[:MAKES]-&gt;(Visit6: Visit {EnterTime: 1488365400000})-[:TO]-&gt;(CheckPoint1)
  
  //by visitor 2:
  CREATE  (Visitor2)-[:MAKES]-&gt;(Visit7: Visit {EnterTime: 1488358800000})-[:TO]-&gt;(CheckPoint1)
  CREATE  (Visitor2)-[:MAKES]-&gt;(Visit8: Visit {EnterTime: 1488360300000})-[:TO]-&gt;(CheckPoint4)
  CREATE  (Visitor2)-[:MAKES]-&gt;(Visit9: Visit {EnterTime: 1488362400000})-[:TO]-&gt;(CheckPoint2)
  CREATE  (Visitor2)-[:MAKES]-&gt;(Visit10: Visit {EnterTime: 1488363000000})-[:TO]-&gt;(CheckPoint1)
  
  //by visitor 3:
  CREATE  (Visitor3)-[:MAKES]-&gt;(Visit11: Visit {EnterTime: 1488353820000})-[:TO]-&gt;(CheckPoint1)
  CREATE  (Visitor3)-[:MAKES]-&gt;(Visit12: Visit {EnterTime: 1488354600000})-[:TO]-&gt;(CheckPoint4)
  CREATE  (Visitor3)-[:MAKES]-&gt;(Visit13: Visit {EnterTime: 1488358200000})-[:TO]-&gt;(CheckPoint3)
  CREATE  (Visitor3)-[:MAKES]-&gt;(Visit14: Visit {EnterTime: 1488359700000})-[:TO]-&gt;(CheckPoint1)  
  </code></pre>
  
  <p>Please, help me write the queries 1 – 4 correctly.
  So far, I have this query, which outputs the visit duration to each checkpoint by a specific visitor (it works correctly): </p>
  
  <pre><code>match (vr: Visitor {MAC: '00:0a:95:9d:68:16'})--&gt;(v1: Visit)--&gt;(cp1: CheckPoint)
  optional match (vr: Visitor {MAC: '00:0a:95:9d:68:16'})--&gt;(v2: Visit)--&gt;(cp2: CheckPoint)
  where v2.EnterTime &gt; v1.EnterTime
  return cp1.CheckPointId, v1.EnterTime, min((v2.EnterTime - v1.EnterTime) / 60000) as visit_duration
  order by v1.EnterTime;
  </code></pre>
  
  <p>But when I am trying to average the visit duration time: </p>
  
  <pre><code>match (vr: Visitor {MAC: '00:0a:95:9d:68:16'})--&gt;(v1: Visit)--&gt;(cp1: CheckPoint)
  optional match (vr: Visitor {MAC: '00:0a:95:9d:68:16'})--&gt;(v2: Visit)--&gt;(cp2: CheckPoint)
  where v2.EnterTime &gt; v1.EnterTime
  return avg(min((v2.EnterTime - v1.EnterTime) / 60000)) as avg_visit_duration;
  </code></pre>
  
  <p>It returns an error message: </p>
  
  <pre><code>Error occurred: Can't use aggregate functions inside of aggregate functions.
  </code></pre>
  
  <p>Probably, I need to use WITH clause, but so far I have been unable to write a query using WITH clause which gives the correct result (110 minutes). </p>
  
  <p>Any ideas? Your help will be much appreciated! Thanks!</p>