In a table you have product_id and order_created_date which is a datetimestamp field. Now we required output as below. For each product_id how many orders are created today, how many orders are created last 5 days, how many orders are created last 30 days, total no of orders created till today
Product_Id orders_today orders_last5days orders_last30days total_orders_tilltoday
101 5 20 100 250
102 7 27 150 450
Hey @siri (haha you knew somebody was going to do that).
Graph databases don't have tables 
Can you show us (maybe with a picture or some cypher MERGE statements) what your data looks like?
Hi @siri - this isn't really a graph-type problem, but we can solve it using a similar technique as we would use in SQL. I don't have your data, so I mocked something up using a random number from 0-100 - this represents the number of days since the order was created. I then use case statements to generate "flags" that represent your time buckets. In my example I use 5 days and 10 days. We can then simply sum those flags. Something like this:
match (n)
with apoc.convert.toInteger(100 * rand()) as d
with case when d<=5 then 1 else 0 end as isIn5Days,
case when d<=10 then 1 else 0 end as isIn10Days
return sum(isIn5Days), sum(isIn10Days)
Sample Query for 30days i tried is as below and similar i can try for last 5 days. My question now is how to club both the outputs to come in single output
WITH duration({days:30}) AS duration
MATCH(p:product) where date(datetime(p.order_created_date))>date()-duration
RETURN Product_Id,size(collect(date(datetime(p.order_created_date)))) as last30days
Thanks a lot steggy for quick reply.
Can you explain me what this line is doing?
with apoc.convert.toInteger(100 * rand()) as d
I mean here Product is the node and Product_Id, order_created_date are property keys.
It's just generating random numbers between 0 and 100. It's a way to get some data to illustrate the query.
How can we write this for order_created_date having values as "2022-09-29T18:53:27.463Z" in the datetimestamp format.
Now if i want use date or use dates in condition how can i write the below line?
with apoc.convert.toInteger(100 * rand()) as d
You want to read this: https://neo4j.com/labs/apoc/4.3/temporal/datetime-conversions/
The above query you gave will also work for dates?
@siri I was giving you a principle to work from. I don't have your data, so I made some up. Instead of making data up like I did, you would write a query that returned the product ID and the number of days between the order date and today. That's your starting point. Then, you can use the CASE expressions like I did to take the number of days between the order date and today and compute the "flags" (just like I did). Finally, return the product_id and the sums of the flags.
If you need some more details - give us some sample data to work with (i.e. - give us some cypher commands that create the data) and show us where you're getting stuck.
Best,
J
Data is as below :
Labels : Product_Id
Properties :
Product_Id : 101
Order_created_date : 2022-09-28T16:35:40.057Z"
Product_Id : 102
Order_created_date : 2022-09-26T10:15:20.677Z"
Product_Id : 101
Order_created_date : 2022-09-15T09:05:20.697Z"
Product_Id : 102
Order_created_date : 2022-10-03T01:15:29.397Z"
Product_Id : 102
Order_created_date : 2022-10-04T03:35:39.399Z"
Now we need to get output as below.
Product_Id
Order_Created_Today
Order_Created_last7days
Order_Created_last30days
Total_Orders_Created
101
0
1
2
2
102
1
1
3
3
@siri I could write the query for you, but I'd rather help you learn how to write the query. Let's piece this together.
First, write a query that will return the product ID and order created date - that is pretty simple.
Next - add to that query to return how many days ago the order was created. Hint: See https://neo4j.com/docs/cypher-manual/current/functions/temporal/ to figure out the current date. You now have a query that returns product_id and number_of_days_since_order_was_created
Next, use case expression to create the 1/0 flags for the "buckets" Your buckets will be 0-1 days, 0-7 days, 0-30 days.
Then, as I showed you, query the product_id and sum up the flags to get the total. You could use another aggregate function to get the COUNT (hint hint) of total orders created.
Take a stab at it. If you can't figure it out, show us what you tried and where you ran into a problem, then I'll be happy to help you out further. But for now, the ball is in your court.
Now i tried this below query but i am not getting product_id, how can i get the sum for each product_id?
Match(p:Product)
WITH CASE WHEN date(datetime(p.order_created_date))>=(date()-duration({days:30})) then 1 else 0 end as last30days,
WITH CASE WHEN date(datetime(p.order_created_date))>=(date()-duration({days:05})) then 1 else 0 end as last5days
return sum(last30days),sum(last5days)
If i am writing product_id in return it is throwing error
with p.product_id as product_id...
I have tried till 2nd query to get number of days the order created.
Match(p:Product)
return p.Product_id,p.order_created_date,duration.inDays(date(datetime(p.order_created_date)),date())days as number_of_days_since_order_was_created
I have written query as below but getting wrong count of orders. can u check and correct it please.
Match(p:Product) where p.product_id in [‘101’,’102’]
WITH duration.inDays(date(datetime(p.order_created_date)),date()).days as d
WITH CASE WHEN d=0 then 1 else 0 end as today,
CASE WHEN d<=30 then 1 else 0 end as last30days,
CASE WHEN d<=7 then 1 else 0 end as last7days,
optional match(p1:Product) where p1.product_id in [‘101’,’102’]
return distinct p1.product_id,sum(today),sum(last30days),sum(last7days)
Can u help me why wrong count is coming, Am i doing anything wrong?
First, that query has at least one syntax error
Second. when I fix the syntax error and run the query, I get this result:
(no changes, no records)
Probably because I don't have your data. If you wanted to provide your data in a format that I can use (e.g. Cypher statements that create the data) and show us the results you are getting and the expected results, maybe someone will help.
More directly: you're asking a question on a public forum and not making it easy for anyone to help you.