How to add output of second query and third query with the output of First Query in neo4j

[siri]

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

[steggy]

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?

[siri]

I mean here Product is the node and Product_Id, order_created_date are property keys.

[siri]

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

[steggy]

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)

[siri]

Thanks a lot steggy for quick reply.

Can you explain me what this line is doing?

with apoc.convert.toInteger(100 * rand()) as d

[siri]

How can we write this for order_created_date having values as "2022-09-29T18:53:27.463Z" in the datetimestamp format.

[steggy]

You want to read this: https://neo4j.com/labs/apoc/4.3/temporal/datetime-conversions/

[steggy]

It's just generating random numbers between 0 and 100. It's a way to get some data to illustrate the query.

[siri]

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

[siri]

The above query you gave will also work for dates?

[steggy]

@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

[siri]

Data is as below :

Labels : Product_Id

Properties :

1.

Product_Id : 101

Order_created_date : 2022-09-28T16:35:40.057Z"

2.

Product_Id : 102

Order_created_date : 2022-09-26T10:15:20.677Z"

3.

Product_Id : 101

Order_created_date : 2022-09-15T09:05:20.697Z"

4.

 Product_Id : 102

Order_created_date : 2022-10-03T01:15:29.397Z"

5.

 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

[steggy · Accepted solution]

@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.

[siri]

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 

[siri]

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

[steggy]

with p.product_id as product_id...

[siri]

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)

[siri]

Can u help me why wrong count is coming, Am i doing anything wrong?

[steggy]

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. 

[siri]

I didn't get any syntax error.

Can you share me what query you ran to correct the syntax error