Using count() and WITH

Neo4j Graph Platform
1

I am trying to right a query to determine the percent of people who purchased a specific product and then also purchased another product.

what_is_happening
what_is_happening1372×398 30.5 KB

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.

should_happen
should_happen1281×399 32.6 KB

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.

2

Assuming count(purch1) = 4952 (which is 'total') ,
(count(purch2) / total) should give you the percentage.

3

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.

4

I am unsure of why "total" is being evaluated to 1.

5

For total, try count(distinct c1) as total

6

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