Banner

 

12 - Graph databases

Answers to exercises

1.
1. Write Cypher code for the following queries.
1a.
How many employees are there in the company?
MATCH (e:Employee)
RETURN count(e) AS `Number of employees`;
1c.
List the products that contain ‘sauce’ in their product description.
MATCH (p:Product)
WHERE p.productName CONTAINS 'Sauce' OR p.productName CONTAINS 'sauce'
RETURN p.productName AS Product;
MATCH (p:Product)
WHERE p.productName =~ '.*[S|s]auce.*'
RETURN p.productName AS Product;
MATCH (p:Product)
WHERE toLower(p.productName) CONTAINS 'sauce'
RETURN p.productName AS Product;
1e.
List in alphabetical order those customers have placed an order.
MATCH (c:Customer)-[r:PURCHASED]->(o:Order)
RETURN DISTINCT c.companyName AS Customer
ORDER BY Customer;
1g.
Which customers have purchased ‘Chai’?
MATCH (c:Customer)-[r1:PURCHASED]->(o:Order)-[r2:CONTAINS]->(p:Product)
WHERE toLower(p.productName) CONTAINS 'chai'
RETURN DISTINCT c.companyName AS Customer;
1i.
List the products in each category.
MATCH (p)-[:PART_OF]->(c)
RETURN c.categoryName AS Category, p.productName AS Product
ORDER BY Category, Product;
1k.
What is the minimum value of a received order?
MATCH (o)-[r:CONTAINS]->(p)
WITH o.orderID AS Order, sum(r.unitPrice*r.quantity) AS Value
RETURN min(Value);
1m.
Report total value of orders for Blauer See Delikatessen.
MATCH (c)-[r1:PURCHASED]->(o)-[r2:CONTAINS]->(p)
WHERE c.companyName = 'Blauer See Delikatessen'
RETURN sum(r2.unitPrice*r2.quantity) AS Value;
1o.
Report those employees who have sold to Blauer See Delikatessen.
MATCH (e)-[r1:SOLD]->(o)<-[r2:PURCHASED]-(c)
WHERE c.companyName = 'Blauer See Delikatessen'
RETURN (e.firstName + ' ' + e.lastName) AS Employee
ORDER BY e.lastName;
2.

Basket of goods analysis: A common retail analytics task is to analyze each basket or order to learn what products are often purchased together. Report the names of products that appear in the same order three or more times.

MATCH (o1:Order)-[r1:CONTAINS]->(p1:Product), (o2:Order)-[r2:CONTAINS]->(p2:Product)
WHERE o1.orderID = o2.orderID AND p1.productName <> p2.productName AND p1.productID > p2.productID
RETURN (p1.productName + ' and ' + p2.productName), COUNT (p1.productName + ' ' + p2.productName) AS Frequency
ORDER BY Frequency DESC;
4.

Same as Last Year (SALY) analysis: Compute the ratio for each product of sales for 1997 versus 1996.

MATCH  (o1:Order)-[r1:CONTAINS]->(p:Product)<-[r2:CONTAINS]-(o2:Order)
WHERE o1.orderDate.year = 1996 AND o2.orderDate.year = 1997
RETURN p.productName AS Product, apoc.math.round(((sum(r1.unitPrice*r1.quantity)/sum(r2.unitPrice*r2.quantity))*100),2) AS `SALY 96/97 as %`
ORDER BY `SALY 96/97 as %` DESC;

This page is part of the promotional and support material for Data Management (open edition) by Richard T. Watson
For questions and comments please contact the author

Date revised: 02-Dec-2022