Rolling Aggregation

[dariusaudryc1]

So I have data model that looks like this:

(u1:User)-[:create]->(trx:transfer)-[:to]->(u2:User)

and in node transfer, I have a transaction date and amount transferred as attributes. So, I'm wondering whether neo4j can give me multiple subgraphs where the total amount exceeds $500 within any one week in between 2 dates (March 1 - March 30, 2019), and only show top 5 with the highest amount?

So my concern was more on doing multiple aggregations with different starting dates. Hopefully, the question is clear enough, but please let me know if it is not.

[kees_vegter]

Hi Darius,

Do do you have a bit more background, how many User nodes and Transfer nodes are there?
And do you mean total amount per week bigger than $500?

I would do something like this (within the same year) and an index on Transfer.date:

WITH date('2019-03-01') as fromDate
,    date('2019-03-30') as toDate
,    500 as minAmount
WITH fromDate, toDate, duration.between(fromDate, toDate).weeks as numberOfWeeks
MATCH (trx:Transfer)
WHERE trx.date >= fromDate
AND   trx.date <= toDate
WITH trx, numberOfWeeks
MATCH (u1:User)-[:create]->(trx)-[:to]->(u2:User)
WITH u1, u2, trx.date.week as week, sum(trx.amount) as weekTotal, collect(trx) as tts 
WHERE weekTotal > minAmount
// ensure that all the weeks are above 500
WITH u1,u2, collect( {week: week, weekTotal: weekTotal, tx: tts}) as data where size(data)  = numberOfWeeks 
UNWIND data as dd
WITH u1,u2, dd.week as week, dd.weekTotal as weekTotal, dd.tx as trfs order by weekTotal desc limit 5
UNWIND trfs as tx
MATCH p=(u1)-[:create]->(tx)-[:to]->(u2)
RETURN p, weekTotal

some remarks:

• I assumed you wanted a graph back, to that is why I used a path in the return
• If the database is very big this query may take too much time. In that you may use Time-trees per User for instance which can also hold some aggregated values....But it is depending on your use case how you should dit it.

I hope you've got some ideas...

Regards
Kees