WITH as GROUP BY: the ER-to-graph recipe and the exam query

From ER diagram to graph model: four rules

Every past paper opens its Neo4j exercise with a graph model of a few ER entities: until 2025 a 1-point "graph model/example describing the nodes, main attributes, and edges"; on 30 Jan 2026, for 2 points, "a written textual representation (no graphical sketches) … using the typical Neo4J notation for nodes ( ), edges -[ ]->, and attributes { }. Include data types for attributes." The session's recipe, "quite simple" (p. 1350), applied to that hospital:

1. An entity becomes a node. "Entities -> Neo4j nodes, maintaining their attributes and using the name of the entity as label." Building becomes (:Building {building_id: String, name: String, year: Integer}).
2. A relationship becomes a relationship. "Relationships -> their equivalent in Neo4j, maintaining their attributes and using the name of the relationship as label." Heats becomes -[:HEATS {start_time: DateTime, end_time: DateTime}]->.
3. An ISA child becomes one node with several labels — below.
4. N:N needs nothing. "N.B. N:N relationship do not need any special treatment" (p. 1351).

Rule 4 saves work: lesson 8 needed a table for every N:N relationship; here many edges are many pairs. Background, not on the slide: an edge has two ends, so a relationship among three entities becomes a node.

ISA: one node, several labels

The ISA rule of p. 1351: "In ISA Hierarchies, parent entities -> nodes, maintaining their attributes, name as label, and relationships (in partial relationships), while children entities -> nodes, maintaining their attributes and relationships and inheriting the ones from the parent entity as well as their label." A child instance is one node wearing the parent's label and its own, with both sets of attributes: the slide's CREATE (:Person:Student {name: "Alice", major: "Physics"}). One person, one node: MATCH (p:Person) finds the students too, and WHERE p:Student picks the subtype without a join.

The coverage letters decide which label sets exist. Partial (P): a person who is neither paramedic nor firefighter is a plain (:Person), the quote's "(in partial relationships)". Total (T): no node wears :HeatingSystem alone. Overlapping (O): "A paramedic can be a firefighter" (6 Jun 2024), so :Person:Paramedic:Firefighter is one node — the slide's "child entities might have multiple labels! (e.g., O - ISA)". Exclusive (E): never two sibling labels on one node.

The model the drills run on

Fig. 1 is the model of the session's eleven AirBnB exercises (p. 1393) — drills 1–11 here, to keep "exercise" for the past papers — and what the papers call "a graph with types". Worth copying: a review is a node with its own identity, author and subject; an amenity is one shared node with a HAS edge from every listing that offers it.

The same model in the 2026 notation, with the attributes the drills use (the slide draws none):

// the AirBnB model of p. 1393: nodes
(:User {name: String})
(:Review)
(:Listing {name: String, accommodates: Integer})
(:Host {name: String})
(:Amenity {name: String})
(:Neighborhood {name: String})
(:City)
(:State {code: String})
(:Country)
// edges
(:User)-[:WROTE]->(:Review)
(:Review)-[:REVIEWS]->(:Listing)
(:Host)-[:HOSTS]->(:Listing)
(:Listing)-[:HAS]->(:Amenity)
(:Listing)-[:IN_NEIGHBORHOOD]->(:Neighborhood)
(:Neighborhood)-[:LOCATED_IN]->(:City)
(:City)-[:IN_STATE]->(:State)
(:State)-[:IN_COUNTRY]->(:Country)

Labels in CamelCase, types in capitals, properties in snake_case with Neo4j's types (String, Integer, Float, Boolean, Date, DateTime, Duration). Fix each direction once: a reversed arrow silently matches nothing. The first two drills read the model one label or type at a time, LIMIT 10 (pp. 1395, 1397):

// ten Review nodes, to inspect their properties (p. 1395)
MATCH (re:Review) RETURN re LIMIT 10

// ten HOSTS relationships with their two end nodes (p. 1397)
MATCH (h:Host)-[r:HOSTS]->(l:Listing) RETURN h, r, l LIMIT 10

WITH is GROUP BY: one row per key

The addendum to the session (pp. 1379–1380) says the grouping part in one sentence: "Cypher doesn't have an explicit GROUP BY keyword; instead, grouping is implied by the non-aggregated variables passed through WITH." Its example counts "the number of times each product has been purchased", beside the SQL it calls equivalent:

-- SQL (p. 1380)
SELECT p.name AS product,
  COUNT(*) AS total
FROM Customer c
JOIN BOUGHT b
  ON c.id = b.customer_id
JOIN Product p
  ON b.product_id = p.id
GROUP BY p.name
ORDER BY total DESC;

// Cypher (p. 1379)
MATCH (c:Customer)-[:BUYS]->
      (p:Product)
WITH p.name AS product,
     count(*) AS purchases
RETURN product, purchases
ORDER BY purchases DESC

MATCH finds all purchases, one row per BUYS edge; WITH turns the rows of each p.name into one row; ORDER BY sorts "by popularity" (Table 1 of p. 1380 starts with Wireless Mouse 45).

RETURN groups the same way: drills 4 and 5 (pp. 1401, 1403) count with no WITH, which you need only to go on after the aggregate — to filter on it (section 3) or to match again (section 5). The two drills differ in the key:

// one row per user node
MATCH (u:User)-[:WROTE]->
      (re:Review)
RETURN u, count(re)

// one row per distinct name
MATCH (u:User)-[:WROTE]->
      (re:Review)
RETURN u.name, count(re)

Two users called Uma merge when the key is the name. Group by the node and return the name beside it: "each artistic director whose name is "Chiara"" means two rows for two Chiaras.

Every item of a WITH or a RETURN that is not aggregated is a grouping key: its presence matters, its position does not.

The exam template: MATCH, WITH, WHERE, RETURN

The first WITH exercise (p. 1365), on the Movies graph of lesson 11, has the shape of every past-paper query: count per entity, keep the entities over a threshold.

// persons with more than one movie, and their count
MATCH (person:Person)-[:ACTED_IN]->(movie)
WITH person, count(*) AS movieCount
WHERE movieCount > 1
RETURN person, movieCount

MATCH: one row per (person, movie). WITH: one row per person, the count aliased. WHERE: "Extract the actors who acted in at least 2 movies".

Two rules hide in it. First, a WHERE after MATCH filters rows, as in drill 3 (p. 1399), "Return the list of listings with accommodates less or equal than 3":

// listings for at most 3 guests (p. 1399)
MATCH (l:Listing)
WHERE l.accommodates <= 3
RETURN l, l.accommodates

The slide adds a habit: swap the last line for RETURN COUNT(l) "to be sure of the correctness of the solution" (2517 on the session's data). A WHERE after WITH, instead, filters groups: "To filter aggregates in Cypher, it is possible to use the WITH and WHERE clauses after aggregation. In SQL, this must be done using the HAVING clause instead" (p. 1380). A count in the first WHERE is refused, "Invalid use of aggregating function count(...) in this context": there, nothing is counted yet.

Second, WITH is a wall: "All data that is not passed through to the WITH statement won't be available in the following part of the query" (p. 1364); "If the person variable wouldn't have been included within the clause, I wouldn't have been able to RETURN it" (p. 1365). A forgotten variable gives "Variable `l` not defined".

Drill 9 (p. 1411), "Return the number of neighborhoods for each state", groups in the RETURN; drill 10 (p. 1412), "Return the name of the states with at least 10 neighborhoods", adds WITH and WHERE. The slides leave both relationships anonymous, [ ], and return s.code (State nodes carry a code); typing the relationships says which one you mean.

// the number of neighbourhoods for each state (p. 1411)
MATCH (s:State)<-[:IN_STATE]-(:City)
      <-[:LOCATED_IN]-(n:Neighborhood)
RETURN s.code, count(n)

// states with at least 10 neighbourhoods (p. 1412)
MATCH (s:State)<-[:IN_STATE]-(:City)
      <-[:LOCATED_IN]-(n:Neighborhood)
WITH s, count(n) AS neighborhood_count
WHERE neighborhood_count >= 10
RETURN s.code, neighborhood_count

"The collection of …": collect, ORDER BY, LIMIT

For "return the collection of", the aggregate is collect(): it packs the values of a group into one list. Drill 8 (p. 1411), "the collection of all the listings' names in the same neighborhood":

// one row per neighbourhood name, with its listings' names
MATCH (l:Listing)-[:IN_NEIGHBORHOOD]->(n:Neighborhood)
RETURN n.name, collect(l.name)

With no key, collect() makes a single list. p. 1366 sorts, cuts and collects — on the Movies graph, one row: ["Zach Grenier", "Wil Wheaton", "Werner Herzog"]. ORDER BY and LIMIT can follow a WITH, not only a RETURN.

// the three names that come last alphabetically, as one list
MATCH (n:Person)
WITH n AS person
ORDER BY person.name DESC
LIMIT 3
RETURN collect(person.name)

p. 1367 shows the last use, "Separate reading from updating"; "Inception" is not in the Movies graph, so read it as a template:

// tag every actor of "Inception" with a second label (p. 1367)
MATCH (p:Person)-[:ACTED_IN]->(m:Movie)
WHERE m.title = "Inception"
WITH p
SET p:InceptionActor
RETURN p.name

A WITH also takes a WHERE out of a pattern: shortest paths

Lesson 11 showed how Neo4j plans a shortestPath (pp. 1368–1373): a fast bidirectional breadth-first search when the condition can be checked while the path grows, and a slower exhaustive depth-first search as a fallback when the condition needs the whole path. A WITH changes that plan. "Predicates used in the WHERE clause that apply to the shortest path pattern are evaluated before choosing the shortest matching path" (p. 1371), but "The inclusion of the WITH clause means that the query plan will not include the fallback to the Slower Exhaustive Search Algorithm" (p. 1372). After WITH the WHERE is a plain filter on the one path already chosen, so the slide labels that query "Not guaranteed to return a result, even if it exists" (p. 1373). The slides' search pictures, their three queries and a widget that runs both searches are in lesson 11.

On the Movies graph (Neo4j 5.26) Tom Hanks acted in Cast Away, one hop. Asked for a longer path, the first query below plans the exhaustive search and finds three hops; the second finds nothing. Keep the bound *..3: with [*] the search ran for over a minute.

// guaranteed: the condition belongs to the pattern
MATCH (a:Person {name: "Tom Hanks"}),
      (b:Movie {title: "Cast Away"}),
      p = shortestPath((a)-[*..3]-(b))
WHERE length(p) > 1
RETURN p

// not guaranteed: finds the one-hop path, then filters it out
MATCH (a:Person {name: "Tom Hanks"}),
      (b:Movie {title: "Cast Away"}),
      p = shortestPath((a)-[*..3]-(b))
WITH p
WHERE length(p) > 1
RETURN p

Conditions on several hops, and zero counts

Exam conditions often sit on a neighbour of the counted node. The recommendation slide shows the tools (p. 1376): "Find actors that "Tom Hanks" hasn't yet worked with, but his co-actors have."

// actors Tom Hanks never worked with, ranked (p. 1376)
MATCH (tom:Person {name:"Tom Hanks"})-[:ACTED_IN]->(m)
        <-[:ACTED_IN]-(coActors),
      (coActors)-[:ACTED_IN]->(m2)<-[:ACTED_IN]-(cocoActors)
WHERE NOT (tom)-[:ACTED_IN]->()<-[:ACTED_IN]-(cocoActors)
  AND tom <> cocoActors
RETURN cocoActors.name AS Recommended, count(*) AS Strength
ORDER BY Strength DESC

The comma joins two patterns on coActors into one four-hop chain. NOT (pattern) is true when no such path exists, and tom <> cocoActors removes Tom himself. RETURN groups by name and count(*) counts routes — Tom Cruise and Zach Grenier lead with 5 on the Movies graph. No WITH: nothing filters the count.

Rows are paths, not people: "Find someone to introduce Tom Hanks to Tom Cruise" (p. 1377) returns five rows, three through Meg Ryan, who made three films with Tom Hanks.

// every route Tom Hanks to Tom Cruise via a co-actor (p. 1377)
MATCH (tom:Person {name:"Tom Hanks"})-[:ACTED_IN]->(m)
        <-[:ACTED_IN]-(coActors),
      (coActors)-[:ACTED_IN]->(m2)
        <-[:ACTED_IN]-(cruise:Person {name:"Tom Cruise"})
RETURN tom, m, coActors, m2, cruise

Two conditions on two neighbours need two variables, as in drill 7 (p. 1407), "listings whose amenities include "First Aid Kit" and "Wireless Internet"":

// listings that offer both amenities (p. 1407)
MATCH (l:Listing)-[r1:HAS]->(a1:Amenity),
      (l)-[r2:HAS]->(a2:Amenity)
WHERE a1.name = "First Aid Kit" AND a2.name = "Wireless Internet"
RETURN l, a1, a2

One amenity variable with a.name = "First Aid Kit" AND a.name = "Wireless Internet" asks one node for two names: 0 rows. "One of which should either be an Ambulance or a Fire Truck" is one node and an OR: WHERE s:Ambulance OR s:FireTruck.

Entities with nothing to count

Drill 6 (p. 1405), "Return the list of users and the number of reviews they wrote", must list users with no review too. MATCH makes no row for them, so the session adds them with a UNION:

// every user and their number of reviews, 0 included (p. 1405)
MATCH (u:User)-[r:WROTE]->(re:Review)
RETURN u, count(re) AS `COUNT`
UNION
MATCH (u:User)
WHERE NOT (u)-[:WROTE]->(:Review)
RETURN u, 0 AS `COUNT`

UNION needs the same column names on both sides, hence AS `COUNT` twice; otherwise "All sub queries in an UNION must have the same return column names".

Not on the slides, but Neo4j's own tool: OPTIONAL MATCH keeps u when the pattern finds nothing, with re null.

// every user and their number of reviews, in one query
MATCH (u:User)
OPTIONAL MATCH (u)-[:WROTE]->(re:Review)
RETURN u, count(re) AS reviews

Two stages: aggregate before you expand again

Drill 11 (p. 1413) asks for two counts over two patterns of the same host: "For each host, return the total number of reviews assigned to their listings with "First Aid Kit" amenity, the total number of listings they own, and their name." The printed solution, in ASCII:

// per host: listings owned, reviews of its kit listings (p. 1413)
MATCH (h:Host)-[:HOSTS]->(l:Listing)<-[:REVIEWS]-(r:Review),
      (l)-[:HAS]->(a:Amenity)
WHERE a.name = "First Aid Kit"
WITH h, count(r) AS reviews_count
MATCH (h)-[:HOSTS]->(li:Listing)
WITH h, reviews_count, count(li) AS all_listings_count
RETURN h.name, all_listings_count, reviews_count

Stage 1 makes one row per review of a kit listing and collapses them into one row per host; stage 2 matches the host's listings from that row, reviews_count riding along, and counts them. On the hook's graph: Ann, 2, 4.

Back to the hook. Without the WITH, each of the four review rows meets both of Ann's listings in the second MATCH: 4 × 2 = 8 rows, and count(li) and count(r) both count rows. With the WITH, the four rows first become one: 1 × 2. For R matching reviews and L listings, the version without the WITH gives

Aggregate to one row per entity before the next MATCH expands it again; otherwise every count after it is multiplied.

DISTINCT repairs the counts, count(DISTINCT li) 2 and count(DISTINCT r) 4, but not a sum: sum(DISTINCT x) adds equal values once, so two different heaters of 0.3 MW would total 0.3. For sums, use the WITH, or WITH DISTINCT on the pair of nodes (section 6).

Mind "for each host" too: stage 1 is a plain MATCH, so a host without a reviewed kit listing vanishes — Bob and Cyd on the test graph. When "for each" must include zeros, make the first pattern optional (Ann 2 4, Bob 1 0, Cyd 1 0), and state your reading.

// every host, with 0 when no kit listing has a review
MATCH (h:Host)
OPTIONAL MATCH (h)-[:HOSTS]->(l:Listing)<-[:REVIEWS]-(r:Review),
      (l)-[:HAS]->(:Amenity {name: "First Aid Kit"})
WITH h, count(r) AS reviews_count
MATCH (h)-[:HOSTS]->(li:Listing)
RETURN h.name, count(li) AS all_listings_count, reviews_count

Two past papers, end to end

No official solutions exist: the model answers below are this notebook's own, each run on a test graph built with its traps.

Exam of 30 Jan 2026, exercise 1: hospital heating (6 PT)

Part 1: "Consider the entities Heating System (and its child entities), Floor, Building, and Department … Provide a written textual representation (no graphical sketches) of the graph model … Include data types for attributes. (2 PT)"

// nodes; (T, E): every heating system wears two labels
(:Building {building_id: String, name: String, year: Integer})
(:Floor {floor_id: String, number: Integer,
    has_elevator: Boolean})
(:Department {department_id: String, specialty: String,
    n_beds: Integer, head_physician_id: String})
(:HeatingSystem:HVACSystem {heater_sys_id: String,
    heating_power: Float, hourly_kwh_cons: Float,
    cost_per_kwh: Float})
(:HeatingSystem:GasHeatingSystem {heater_sys_id: String,
    heating_power: Float, hourly_sqm_cons: Float,
    cost_per_sqm: Float})
// edges
(:Building)-[:HAS_FLOOR]->(:Floor)
(:Department)-[:PLACED_INTO]->(:Floor)
(:HeatingSystem)-[:HEATS {start_time: DateTime,
    end_time: DateTime}]->(:Floor)

Part 2: "Write a Cypher query that returns, for every Department occupying more than 2 Floors in the same Building, the total heating power of all their Heaters. Filter the results to show only those Departments whose total power exceeds 0.5 MWh and sort them in descending order by power. (2 PT)" Three stages, two guards: WITH DISTINCT d (a department passing in two buildings) and WITH DISTINCT d, h (a heater on two of its floors). Assumptions to write down: "their Heaters" are all the heaters of the department's floors, in any building; power is in MW, so "0.5 MWh" is read as 0.5 MW.

// departments on more than 2 floors of one building: power > 0.5
MATCH (d:Department)-[:PLACED_INTO]->(f:Floor)
      <-[:HAS_FLOOR]-(b:Building)
WITH d, b, count(DISTINCT f) AS floors
WHERE floors > 2
WITH DISTINCT d
MATCH (d)-[:PLACED_INTO]->(:Floor)<-[:HEATS]-(h:HeatingSystem)
WITH DISTINCT d, h
WITH d, sum(h.heating_power) AS total_power
WHERE total_power > 0.5
RETURN d.department_id AS department, total_power
ORDER BY total_power DESC

Test graph: a 0.3 MW heater heats two floors of one department; 0.75 with the guard, 1.05 without. Part 3: "Write a Cypher query that returns all Buildings built after 1967 with a total number of beds greater than or equal to 20, located on floors with an elevator or located on the ground level. (2 PT)" The beds are those of the departments on qualifying floors; the ground level is assumed to be number 0.

// buildings after 1967 with at least 20 beds on qualifying floors
MATCH (b:Building)-[:HAS_FLOOR]->(f:Floor)
      <-[:PLACED_INTO]-(d:Department)
WHERE b.year > 1967 AND (f.has_elevator = true OR f.number = 0)
WITH DISTINCT b, d
WITH b, sum(d.n_beds) AS beds
WHERE beds >= 20
RETURN b.building_id AS building, b.name AS name, beds

Without WITH DISTINCT b, d, a department on two qualifying floors adds its beds twice: 42 instead of 21 on the test graph.

Exam of 6 Jun 2024, exercise 2: natural disasters (5 PT)

Part 1: "Consider the entities Person, Paramedic, Firefighter, Vehicle, Ambulance, and Fire Truck … Sketch a graph model/example … Either show an example graph or a graph with types. (1 PT)" Fig. 3 is an example graph; the ISA widget of section 1 builds the typed list.

Part 2: "Write a Cypher query to collect the number of people who drive at least 5 different vehicles, one of which should either be an Ambulance or a Fire Truck (2 PT)". Stage 1 counts different vehicles per person, stage 2 looks for a special one; count(DISTINCT p) matters, since a person with an ambulance and a fire truck makes two rows.

// people with at least 5 different vehicles, one of them special
MATCH (p:Person)-[:DRIVES]->(v:Vehicle)
WITH p, count(DISTINCT v) AS vehicles
WHERE vehicles >= 5
MATCH (p)-[:DRIVES]->(s:Vehicle)
WHERE s:Ambulance OR s:FireTruck
RETURN count(DISTINCT p) AS people

On the test graph: 7, and 20 with both DISTINCTs removed. Part 3: "Write a Cypher query to return the collection of Fire Trucks with a water capacity greater than 100 litres that were driven by at least 5 Firefighters named John, who also drove 10 non-Fire Truck vehicles each. (2 PT)" Three stages, from the end of the sentence: Johns with at least 10 other vehicles ("10" read as at least 10), such Johns per fire truck, the collection.

// fire trucks over 100 l, 5+ Johns with 10+ other vehicles
MATCH (f:Firefighter {name: "John"})-[:DRIVES]->(v:Vehicle)
WHERE NOT v:FireTruck
WITH f, count(DISTINCT v) AS others
WHERE others >= 10
MATCH (f)-[:DRIVES]->(t:FireTruck)
WHERE t.water_capacity > 100
WITH t, count(DISTINCT f) AS johns
WHERE johns >= 5
RETURN collect(t) AS fire_trucks

A checklist for answering without a database

Write each answer as if nothing could run it: the session's count check (p. 1399) then happens in your head.

1. Model first: labels from entity names, ISA labels by T/P and E/O, types from relationship names, one direction per type.
2. Rewrite the question as "for each key, aggregate of what, considering only …": is the key a node or a name?
3. Conditions on one row go after MATCH; conditions on counts and sums after the WITH that computes them.
4. "Different": count(DISTINCT …) — can two paths reach the same thing?
5. Two counts: two stages, carrying every variable you still need through each WITH.
6. A sum over things two paths can reach: WITH DISTINCT on the pair, never sum(DISTINCT …).
7. "For each X" with zeros: OPTIONAL MATCH and count(y), or UNION with equal column names.
8. Names as in your model, ASCII arrows, dates as date("2024-08-18"), assumptions written down.
9. Run it on five nodes in your head: what should come out, and what does your query return?