Head's Up! These forums are read-only. All users and content have migrated. Please join us at community.neo4j.com.
07-29-2021 08:22 AM
I am trying to right a query to determine the percent of people who purchased a specific product and then also purchased another product.
The query is evaluating "total" to 1 every time which is why I am getting the results I am.
What I want to be happening is this.
I just hard coded the number in. I just need help finding a way around this so I can get what is happening in the second picture without having to hard code the "total" in.
Solved! Go to Solution.
07-29-2021 11:51 AM
As you are holding the customers ("with ..., c1"), you will have one line for each customer. If we concider that all customer only buy one p1, your "count(p1) as total" will be always 1, but in many rows.
Try first following query:
match (c1:Customer)-[purch1:PURCHASED]-(p1:Product)
where p1.item_id = "81316"
with count(purch1) as total, c1
return total
then try this without c1:
match (c1:Customer)-[purch1:PURCHASED]-(p1:Product)
where p1.item_id = "81316"
with count(purch1) as total
return total
therefore you should avoid to overtake c1.
Following query is the same (it's just a different way to say products that are not "p1" are all "p2"), but the need of reuse c1 is avoided:
match (p1:Product)-[purch1:PURCHASED]-(c1:Customer)-[purch2:PURCHASED]-(p2:Product)
where p1.item_id = "81316"
return p1.item_id, count(purch1), p2.item_id, count(purch2), count(purch2)/count(purch1) as percents
order by percents desc limit 10
07-29-2021 09:48 AM
Assuming count(purch1) = 4952 (which is 'total') ,
(count(purch2) / total) should give you the percentage.
07-29-2021 09:54 AM
Yes, that is what I try to do in the first query stated. However, for some reason "total" is being evaluated as 1 when I want it to be 4952 which messes up my percentages calculations.
07-29-2021 09:55 AM
I am unsure of why "total" is being evaluated to 1.
07-29-2021 10:11 AM
For total, try count(distinct c1) as total
07-29-2021 11:51 AM
As you are holding the customers ("with ..., c1"), you will have one line for each customer. If we concider that all customer only buy one p1, your "count(p1) as total" will be always 1, but in many rows.
Try first following query:
match (c1:Customer)-[purch1:PURCHASED]-(p1:Product)
where p1.item_id = "81316"
with count(purch1) as total, c1
return total
then try this without c1:
match (c1:Customer)-[purch1:PURCHASED]-(p1:Product)
where p1.item_id = "81316"
with count(purch1) as total
return total
therefore you should avoid to overtake c1.
Following query is the same (it's just a different way to say products that are not "p1" are all "p2"), but the need of reuse c1 is avoided:
match (p1:Product)-[purch1:PURCHASED]-(c1:Customer)-[purch2:PURCHASED]-(p2:Product)
where p1.item_id = "81316"
return p1.item_id, count(purch1), p2.item_id, count(purch2), count(purch2)/count(purch1) as percents
order by percents desc limit 10
All the sessions of the conference are now available online