Compose a Multi-Stage Analytical Query with Parameters, Aggregation, Subqueries, and Explainable Intermediate Results

Learning outcomes

AtlasMart's reporting API now needs a reviewable query that starts from customer filters, computes order and product summaries independently, applies a gross-spend threshold, and returns one bounded map per customer. The goal is not clever Cypher; it is an analytical pipeline whose cardinality and scope can be explained stage by stage.

Re-establish the deterministic AtlasMart read fixture

Every lesson can stand alone. The fixture is idempotent, so rerunning it is the safest reset: it restores four customers, five orders, four products, two categories, five PLACED, seven CONTAINS, and four IN_CATEGORY relationships without deleting the Chapter 04 operations graph. If your course sandbox already contains the fixture, this simply normalizes the properties used here.

CYPHER 25CREATE CONSTRAINT customer_id IF NOT EXISTS FOR (c:Customer) REQUIRE c.customerId IS UNIQUE;CREATE CONSTRAINT product_id IF NOT EXISTS FOR (p:Product) REQUIRE p.productId IS UNIQUE;CREATE CONSTRAINT order_id IF NOT EXISTS FOR (o:Order) REQUIRE o.orderId IS UNIQUE;CREATE CONSTRAINT category_id IF NOT EXISTS FOR (c:Category) REQUIRE c.categoryId IS UNIQUE;MERGE (c1:Customer {customerId:'C-1001'}) SET c1.name='Ava Chen', c1.tier='gold', c1.region='eu'MERGE (c2:Customer {customerId:'C-1002'}) SET c2.name='Noah Smith', c2.tier='silver', c2.region='us'MERGE (c3:Customer {customerId:'C-1003'}) SET c3.name='Mina Rahimi', c3.tier='gold', c3.region='me'MERGE (c4:Customer {customerId:'C-1004'}) SET c4.name='Leo Martin', c4.region='eu'MERGE (cat1:Category {categoryId:'CAT-CAMERAS'}) SET cat1.name='Cameras'MERGE (cat2:Category {categoryId:'CAT-AUDIO'}) SET cat2.name='Audio'MERGE (p1:Product {productId:'P-1001'}) SET p1.name='Trail Camera', p1.price=129.90, p1.rating=4.7MERGE (p2:Product {productId:'P-1002'}) SET p2.name='Studio Headphones', p2.price=89.00, p2.rating=4.7MERGE (p3:Product {productId:'P-1003'}) SET p3.name='Action Camera', p3.price=219.00, p3.rating=4.5MERGE (p4:Product {productId:'P-1004'}) SET p4.name='USB Microphone', p4.price=75.00MERGE (p1)-[:IN_CATEGORY]->(cat1)MERGE (p3)-[:IN_CATEGORY]->(cat1)MERGE (p2)-[:IN_CATEGORY]->(cat2)MERGE (p4)-[:IN_CATEGORY]->(cat2)MERGE (o1:Order {orderId:'O-2001'}) SET o1.placedAt=datetime('2026-08-01T09:00:00Z'), o1.status='paid', o1.total=218.90MERGE (o2:Order {orderId:'O-2002'}) SET o2.placedAt=datetime('2026-08-02T10:30:00Z'), o2.status='paid', o2.total=219.00MERGE (o3:Order {orderId:'O-2003'}) SET o3.placedAt=datetime('2026-08-03T12:00:00Z'), o3.status='shipped', o3.total=129.90MERGE (o4:Order {orderId:'O-2004'}) SET o4.placedAt=datetime('2026-08-04T14:15:00Z'), o4.status='paid', o4.total=164.00MERGE (o5:Order {orderId:'O-2005'}) SET o5.placedAt=datetime('2026-08-04T14:15:00Z'), o5.status='paid', o5.total=75.00MERGE (c1)-[:PLACED]->(o1)MERGE (c2)-[:PLACED]->(o2)MERGE (c1)-[:PLACED]->(o3)MERGE (c3)-[:PLACED]->(o4)MERGE (c4)-[:PLACED]->(o5)MERGE (o1)-[:CONTAINS {quantity:1}]->(p1)MERGE (o1)-[:CONTAINS {quantity:1}]->(p2)MERGE (o2)-[:CONTAINS {quantity:1}]->(p3)MERGE (o3)-[:CONTAINS {quantity:1}]->(p1)MERGE (o4)-[:CONTAINS {quantity:1}]->(p2)MERGE (o4)-[:CONTAINS {quantity:1}]->(p4)MERGE (o5)-[:CONTAINS {quantity:1}]->(p4);

Before analytical work, verify the relevant slice rather than assuming the whole database contains only these entities:

CYPHER 25MATCH (c:Customer) WITH count(c) AS customersMATCH (o:Order) WITH customers, count(o) AS ordersMATCH (p:Product) WITH customers, orders, count(p) AS productsMATCH (:Customer)-[placed:PLACED]->(:Order)WITH customers, orders, products, count(placed) AS placedMATCH (:Order)-[line:CONTAINS]->(:Product)RETURN customers, orders, products, placed, count(line) AS contains;

For the fixture above the invariant is 4 / 5 / 4 / 5 / 7. If Chapter 04 remains loaded, the total database node/relationship counts will be larger; label- and type-specific counts are the contract for this chapter.

1. Write the result contract before the query

For this lab the endpoint accepts $region (nullable) and $minGross. It returns one map per qualifying customer with stable customer identity, name, region, order count, gross order total, line-unit count, and a deduplicated bounded set of product IDs. With $region = null and $minGross = 150.0, the deterministic fixture should qualify three customers: Ava, Noah and Mina. That expectation is a fixture invariant, not a benchmark.

2. Compose two correlated one-row aggregates

CYPHER 25MATCH (c:Customer)WHERE $region IS NULL OR c.region = $regionCALL (c) {  MATCH (c)-[:PLACED]->(o:Order)  RETURN count(o) AS orderCount,         round(sum(o.total), 2) AS gross,         min(o.placedAt) AS firstOrderAt,         max(o.placedAt) AS lastOrderAt}CALL (c) {  MATCH (c)-[:PLACED]->(:Order)-[line:CONTAINS]->(p:Product)  RETURN sum(line.quantity) AS units,         collect(DISTINCT p.productId) AS productIds}WITH c, orderCount, gross, firstOrderAt, lastOrderAt, units, productIdsWHERE gross >= $minGrossRETURN c{  .customerId,  .name,  .region,  orderCount: orderCount,  gross: gross,  firstOrderAt: firstOrderAt,  lastOrderAt: lastOrderAt,  units: units,  productIds: productIds} AS customerSummaryORDER BY customerSummary.gross DESC, customerSummary.customerId;

Each CALL (c) is deliberately aggregated to one row before returning. This keeps the outer grain stable. The product list uses DISTINCT because the response contract asks for unique product IDs, while units preserves relationship quantity separately.

3. Make intermediate evidence executable

Do not debug only the final query. Run each stage independently with a known customer. For C-1001, the fixture contains two orders and three CONTAINS relationships across those orders. The snippets below prove the row source before aggregation.

CYPHER 25MATCH (:Customer {customerId:'C-1001'})-[:PLACED]->(o:Order)RETURN o.orderId, o.totalORDER BY o.orderId;

CYPHER 25MATCH (:Customer {customerId:'C-1001'})-[:PLACED]->(o:Order)-[line:CONTAINS]->(p:Product)RETURN o.orderId, p.productId, line.quantityORDER BY o.orderId, p.productId;

Only after those row sources are understood should the aggregates be trusted. On a larger graph, use EXPLAIN before execution and PROFILE on safe representative workloads; record actual rows, database hits and memory-related evidence rather than copying plan numbers from this course.

4. Deliberately wrong: disconnected catalog expansion

The following query looks like it is building a customer summary, but the disconnected (p:Product) multiplies each customer-order row by every product in the catalog before aggregation.

CYPHER 25MATCH (c:Customer)-[:PLACED]->(o:Order), (p:Product)RETURN c.customerId AS customerId,       count(DISTINCT o) AS orderCount,       collect(DISTINCT p.productId) AS productIds;

count(DISTINCT o) can make the order count look correct, and collect(DISTINCT p.productId) returns a tidy list—but it is the entire catalog for every customer. The repair is not “add more DISTINCT.” It is to connect products through (o)-[:CONTAINS]->(p) or isolate an actually independent catalog statistic in CALL ().

CYPHER 25MATCH (c:Customer)-[:PLACED]->(o:Order)-[:CONTAINS]->(p:Product)RETURN c.customerId AS customerId,       count(DISTINCT o) AS orderCount,       collect(DISTINCT p.productId) AS purchasedProductIdsORDER BY customerId;

5. Add an EXISTS gate when the API has a boolean prerequisite

If a caller asks for summaries only for customers who have ever bought a camera, filter with an existence condition before doing heavier per-customer aggregation. This states the intent directly and avoids exporting every qualifying path merely to deduplicate the customer later.

CYPHER 25MATCH (c:Customer)WHERE EXISTS {  MATCH (c)-[:PLACED]->(:Order)-[:CONTAINS]->(:Product)-[:IN_CATEGORY]->(:Category {categoryId:'CAT-CAMERAS'})}RETURN c.customerId, c.nameORDER BY c.customerId;

6. Lab verification matrix

Because this is read-only, cleanup is simply parameter reset. If fixture state has drifted, rerun the idempotent fixture. Do not delete unrelated graph data or the Chapter 04 handoff graph.

Production judgment

This query shape is maintainable because every stage has an explicit grain and correlation, not because subqueries are always faster. On production data, evaluate anchor selectivity, indexes on stable domain identifiers and commonly filtered properties, graph degree, order-history distribution, size of collect(DISTINCT ...), transaction/query memory, driver fetch/stream behavior, timeouts, cancellation and response-size limits. A reporting endpoint that grows toward unbounded history or large cross-customer analytics may belong in a separate analytical architecture rather than an ever-larger transactional Cypher request.

Chapter 06 moves from reads to writes. The same discipline becomes more important there: row multiplication can create duplicate mutations, retries can repeat side effects, and MERGE is not a universal SQL upsert.

Summary and next step

Chapter 05 closes with a compositional rule: know the row grain entering every clause, know which values are lists or maps, know exactly what a subquery imports and returns, and treat cardinality as an observable design property. Next, apply that discipline to graph mutations, idempotency and concurrency in Chapter 06.

Authoritative references

• Current Neo4j versions — Release/LTS snapshot used to pin the course baseline.
• Cypher Manual — Introduction — Cypher 25 status and language-version policy.
• Select Cypher version — Database/default and per-query Cypher version behavior.
• Aggregating functions — Aggregate and grouping semantics.
• CALL subqueries — Modern variable-scope clause and row/cardinality behavior.
• EXISTS subqueries — Existence filters and scope semantics.
• Map expressions — Map access and projection behavior used for API-shaped output.
• UNWIND — List-to-row semantics used throughout the chapter.