cancel
Showing results for 
Search instead for 
Did you mean: 

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

siri
Node Link

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

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

View solution in original post

20 REPLIES 20

steggy
Neo4j
Neo4j

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?

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

siri
Node Link

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
Neo4j
Neo4j

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)



Thanks a lot steggy for quick reply.

Can you explain me what this line is doing?

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

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

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

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

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 :

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

@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 

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

with p.product_id as product_id...

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. 

I didn't get any syntax error.

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

Nodes 2022
Nodes
NODES 2022, Neo4j Online Education Summit

On November 16 and 17 for 24 hours across all timezones, you’ll learn about best practices for beginners and experts alike.