How can I get the prices that are lower than the average?

[fatjon_lala]

I have a node labeled as Price and I want to retrieve the prices that are lower than an average over these prices. I tried the following cypher query but it did not work out. Can anyone help me out with this? PS: even if you do it with one match it gives an error when you use p.monthly twice inside a query....

MATCH (prod:Product)<-[:OFFERS_PRODUCT]-(p:Provider)<-[:IS_PROVIDED]-(s:Service)-[:IS_SECURED]->(sec:Security)
WITH rand() AS Random, prod.product_name AS Product_Name
WHERE sec.anti_ddos = 'yes'
RETURN Product_Name
ORDER BY Random
LIMIT 5

[michael_hunger]

You have two options.

Either you do your query twice, once with the aggregation to get the average and filter then in a 2nd query

MATCH (p:Product)
WITH avg(p.price) as avgPrice
MATCH (p:Product) 
WHERE p.price > avgPrice
RETURN p

Or you do one query where you not only aggregate the average but also the data (almost like a window function) and then UNWIND and filter.

MATCH (p:Product)
WITH avg(p.price) AS avgPrice, collect(p) AS products
UNWIND products AS p
WITH p WHERE p.price > avgPrice
RETURN p

Or if you're fine returning a collection then this is shorter

MATCH (p:Product)
WITH avg(p.price) as avgPrice, collect(p) as products
RETURN [p IN products WHERE p.price > avgPrice] as overAvgProducts

[fatjon_lala]

@michael.hunger Well this is an idea that I also thought about but is not working. I just want to retrieve a list of products that have the price lower than the average. So I am defining some rules in my database, using python coding and I have defined a rule called cheap. This rule should be connected to the price and give me back all the product that have a price lower than this average. I have a node that keeps the Prices and a node that keeps the Products. I just want to make a cypher query that will give me this information so I can use it in my python programming. Thanks..

[michael_hunger]

What exactly is not working, can you share your model + code so far in more detail.

[fatjon_lala]

Yes. This is the model I have defined for the data that I have:
(p:Price)<-[:COSTS]-(s:Service)-[:IS_PROVIDED]->(pr:Provider)-[:OFFERS_PRODUCT]->(prod:Product). At the price node I have monthly price, yearly price and currency. Now what I want to do is to find the average of prices per product and then to get the products name which have a price lower than that average. I already tried this query:
MATCH (p:Price)<-[:COSTS]-(s:Service)-[:IS_PROVIDED]->(pr:Provider)-[:OFFERS_PRODUCT]->(prod:Product)
WITH avg(toInteger(p.monthly)) as avgPrice, prod.product_name as Product, s.service_name as Service, p.currency as Currency
RETURN avgPrice, Currency, collect(distinct Service)
to get the average price for distinct services. I want just now to be able to run a single query which would also do the selection of the cheap products??

[mike_r_black]

If you have flexibility in your data model, I'm also wondering if a Nested Set model would be more "graphy" and perform better? I was trying think if there would be some way to model the data sort of like a B-Tree or something that you could link the products to a :Price node and that node would know where it was in an organized model. Then at query time you're doing graph traversals instead of scanning all the nodes and calculating an aggregate.

[fatjon_lala]

Well I just am new to neo4j and I might not know all the possible ways to retrieve information from the database. The problem is that I just need to get the products that have a cheaper price than average and I thought about this way. If you have any idea of using the Nested Set Model you can share it with me. I am looking forward and thanks.

[michael_hunger]

You are aggregating per product, you need to remove "prod.product_name" from that list to get the average per service.

MATCH (p:Price)<-[:COSTS]-(s:Service)-[:IS_PROVIDED]->(pr:Provider)-[:OFFERS_PRODUCT]->(prod:Product)
WITH avg(toInteger(p.monthly)) as avgPrice, s.service_name as Service, p.currency as Currency

I recommend you spend a few minutes in the online training, that helps you get more familiar with Cypher.