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`;
1b.
Prepare a list of employees by last name, first name, and job title. Sort by last name.
MATCH (e:Employee) 
RETURN e.lastName AS Last, e.firstName AS First, e.title AS Title
ORDER BY Last;
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;
1d.
In what category are sauces?
MATCH (c:Category)
WHERE toLower(c.description) CONTAINS 'sauce'
RETURN c.categoryName AS Category;
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;
1f.
List in alphabetical order those customers have not placed an order.
MATCH (c:Customer)-[r:PURCHASED]->(o:Order)
  WITH COLLECT (DISTINCT c.customerID) AS Ordered
  MATCH (c:Customer)
  WHERE  NOT (c.customerID IN Ordered)
  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;
1h.
List the amount ordered by each customer by the value of the order.
MATCH (c)-[r1:PURCHASED]->(o)-[r2:CONTAINS]->(p)
RETURN c.companyName AS Customer,  round(sum(r2.unitPrice*r2.quantity)) AS Value
ORDER BY Value DESC;
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;
1j.
How many products in each category?
MATCH (p)-[:PART_OF]->(c)
WITH c.categoryName AS Category, count(p.productName) as Count
RETURN Category, Count
ORDER BY Category;
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);
1l.
Who is the customer who placed the minimum value order?
MATCH (c)-[r1:PURCHASED]->(o)-[r2:CONTAINS]->(p)
WITH c.companyName AS Customer, o.orderID AS Order, sum(r2.unitPrice*r2.quantity) AS Value
RETURN Customer, Value
ORDER BY Value
LIMIT 1;
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;
1n.
Who reports to Andrew Fuller? Report by last name alphabetically and concatenate first and last names for each person.
MATCH(e)-[:REPORTS_TO]->(m)
WHERE m.firstName = 'Andrew' AND m.lastName = 'Fuller'
RETURN (e.firstName +  ' ' + e.lastName) AS Employee
ORDER BY e.lastName;
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;
1p.
Report the total value of orders by year
MATCH (o)-[r:CONTAINS]->(p)
RETURN o.orderDate.year as Year, round(sum(r.unitPrice*r.quantity)) AS Value
ORDER BY Year
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;
3.

ABC reporting: Compute the revenue generated by each customer based on their orders. Also, show each customer's revenue as a percentage of total revenue. Sort by customer name. 

MATCH  (o:Order)-[r:CONTAINS]->(p:Product)
WITH round(sum(r.unitPrice*r.quantity)) as Total
MATCH  (c:Customer)-[r1:PURCHASED]->(o:Order)-[r2:CONTAINS]->(p:Product)
RETURN c.companyName AS Customer, round(sum(r2.unitPrice*r2.quantity)) AS Value,
round(sum(r2.unitPrice*r2.quantity)/Total*100) AS Percent
ORDER BY Value 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: 10-Dec-2021