Urgently Need help designing query

[madan_aman]

Hi, I have setup following relationship

Customer - [Buys] -> Stocks

I am pulling most popular stock as

MATCH (n:Customer)-[:BUYS]->(s:Stock) RETURN s,count(distinct n) as cnt order by cnt desc limit 1

Buy relation have Quantity, unit price and invoice date properties.

I need to find 2 things.

1. What did customer buy before and after purchasing most popular stock
2. Display the customer who placed orders in the most consecutive calendar months?

Any guidance on this shall be very helpful.

[michael_hunger]

It might make sense to model the transaction as it's own node for more involved querying/operations.

1. get the most popular stock and use it's time as the median to filter other purchases by

WHERE abs(popularBuy.timestamp - buy.timestamp) < X
or

WHERE popularBuy.timestamp - duration('3D') < buy.timestamp < popularBuy.timestamp + duration('3D')

compute the delta between last and first month as duration
starting from first month add a duration of +1 for each month

create a boolean list with true, false for each month and then a reduce that computes the running totals

reduce(result=[0,0], x in bought | case when x then [result[0], result[1]+1] else case result[1] > result[0] then [result[1],0] else [result[0],0] end)
and then result[0] has your highest consecutive

you can of course also combine that into one operation

alternatively compute the "has bought previous month" and aggregate them e.g. on the min or max month of consecutive purchases

[metadeepak1]

Hi,

Are you able to find the solution for these queries?
Kindly share.

Thanks,
Ankit Gupta