Skip to content
Tutorials Center
My learning

Topics

Lesson 27 of 30 ยท Practice

Practice exercises with solutions

Eight hands-on exercises for CQL and Cypher: data modeling, query rules, consistency math, friend suggestions and MERGE, with worked solutions.

You learn modeling by doing it. This lesson gives you eight exercises, four for Cassandra (CQL) and four for Neo4j (Cypher), each with a worked solution. Try each one on paper first, then compare. For some of them a runnable stand-in lets you check your answer: Python code in your browser that applies the same rules the real databases apply.

As on the rest of this site, neither Cassandra nor Neo4j runs here, so the CQL and Cypher in code blocks are read-only. The Python programs are honest stand-ins: the first checks queries against the key rules of CQL, the second implements graph logic on a dictionary. Use them to test ideas, and use a real database (a local container is enough) to confirm the final syntax.

Part A: Cassandra

Exercise 1: model a chat

A chat application shows the latest 50 messages in a room, newest first, and lets users scroll back in time. Rooms can be busy: some receive 20 messages per second for years. Design the table.

Think about: what is the query, what goes in the partition key, and what stops a partition from growing forever.

Solution. The query names a room and wants a time-ordered slice, so room_id belongs in the partition key and the message time is a clustering column. A single partition per room would grow without bound for a busy room, so add a time bucket, for example the month.

SQL
CREATE TABLE messages_by_room (
  room_id uuid,
  month   text,         -- '2026-03'
  sent    timeuuid,
  sender  text,
  body    text,
  PRIMARY KEY ((room_id, month), sent)
) WITH CLUSTERING ORDER BY (sent DESC);

SELECT sent, sender, body FROM messages_by_room
 WHERE room_id = ? AND month = '2026-03' LIMIT 50;

At 20 messages per second a month holds around 52 million rows, which is far too large, so for the busiest rooms a day or hour bucket is needed. The right bucket depends on the busiest partition, not the average one. If the current bucket has fewer than 50 messages, the application reads the previous bucket too. See Time-series and bucketing patterns.

Take two tables from the event and order store project, orders_by_user with PRIMARY KEY ((user_id), placed, order_id) and events_by_user_day with PRIMARY KEY ((user_id, day), ts). For each query decide whether Cassandra accepts it without ALLOW FILTERING. The rules you need are in Partition keys and clustering keys:

  • every partition key column must be restricted with = (or IN);
  • clustering columns must be restricted from the left, with no gaps;
  • an inequality (range) is allowed on one clustering column, and no later column may be restricted;
  • columns outside the key can only be restricted with an index or ALLOW FILTERING.

The stand-in below encodes those four rules. Predict each answer first, then run it.

Python A checker for CQL key rules

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Solution. Queries 1, 2, 6, 9 and 10 are accepted. Query 3 skips placed, so order_id has a gap. Query 4 forgets the partition key. Query 5 filters on a plain column. Query 7 adds an equality after a range. Query 8 supplies only half of the composite partition key. The program prints exactly these verdicts, with the reason for each failure. Real Cassandra words the errors differently and has a few extra rules (for example around IN and multi-column restrictions), so treat the stand-in as a study aid.

Exercise 3: choose consistency levels

A cluster runs with a replication factor of 3 in a single data center. For each operation pick a read and a write level and say how many replica failures it survives:

  1. A bank-style balance that must never be read stale.
  2. A "last seen" timestamp that can be a few seconds old.
  3. A write-heavy click log where losing a few events is acceptable.

Solution. Quorum is floor(RF / 2) + 1, which is 2 for RF 3. A read and a write overlap when R + W > RF.

Case Write Read W + R Failures survived
1 QUORUM QUORUM 4 > 3 1 replica down for reads and writes
2 ONE ONE 2, no overlap 2 replicas down, but reads may be stale
3 ONE ONE 2 2 replicas down, fastest

For case 1, balance logic that depends on read-then-write (for example "only withdraw if enough money") needs more than quorum reads: it needs a lightweight transaction (IF conditions) from Lightweight transactions, batches and counters, because two clients can both read the old balance. For multi-data-center deployments, use LOCAL_QUORUM (Replication and tunable consistency).

Exercise 4: size a partition

Sensors write one reading per second. Each stored reading is about 100 bytes including overhead. You are choosing between a day bucket and an hour bucket. Compute the rows and approximate bytes per partition for each.

Solution. A day has 86,400 seconds: 86,400 rows, about 8.6 MB per partition. An hour has 3,600 rows, about 360 KB. A commonly quoted rule of thumb is to keep partitions well under 100 MB and under a few hundred thousand rows, so both work for one reading per second. If a sensor reports every 10 milliseconds, a day is 8,640,000 rows (about 864 MB), and the hour bucket (360,000 rows, about 36 MB) becomes the safer choice. The exact limits depend on your hardware and workload, so measure with realistic data. The trade-off: smaller buckets mean more partitions to read for a long time range.

Part B: Neo4j and Cypher

The next exercises use a small network. An arrow means "follows".

flowchart LR
    Ada --> Ben
    Ada --> Cy
    Ben --> Cy
    Ben --> Dee
    Cy --> Dee
    Dee --> Eli
    Eli --> Ada
    Fay

Six users: Fay follows nobody and nobody follows her.

Exercise 5: suggest people to follow

Suggest users to Ada: people followed by the people Ada follows, but not yet followed by Ada and not Ada herself. Rank by the number of different people who lead to the suggestion.

Solution. In Cypher, that is a two-hop pattern with a filter, followed by a grouped count:

CYPHER
MATCH (me:User {name: 'Ada'})-[:FOLLOWS]->(friend)-[:FOLLOWS]->(suggestion)
WHERE suggestion <> me
  AND NOT (me)-[:FOLLOWS]->(suggestion)
RETURN suggestion.name AS name, count(DISTINCT friend) AS via
ORDER BY via DESC, name;

count(DISTINCT friend) counts the distinct friends, so each friend contributes once even if several routes exist. The stand-in below runs the same logic.

Exercise 6: users who follow nobody

List every user together with how many people they follow, including users who follow no one.

Solution. An ordinary MATCH drops users without a match. Use OPTIONAL MATCH, which keeps the row and returns null for the missing part, and count() of a null-valued variable is 0:

CYPHER
MATCH (u:User)
OPTIONAL MATCH (u)-[:FOLLOWS]->(f)
RETURN u.name AS name, count(f) AS following
ORDER BY following DESC, name;

Both Part B answers are computed by this stand-in.

Python Friend suggestions and OPTIONAL MATCH in Python

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Dee is the only suggestion: Ben and Cy both follow her, and everyone else Ada can reach through a friend is already followed by Ada. Fay appears with a count of 0 only in the OPTIONAL MATCH version.

Exercise 7: make an import repeatable

You load likes.csv (columns user, movie) every night. Write the statement so that running it twice produces no duplicate users, movies or LIKES relationships.

Solution. Use MERGE on each piece, never CREATE, and back each MERGE with a uniqueness constraint so that it is fast and safe under concurrency:

CYPHER
CREATE CONSTRAINT user_name  IF NOT EXISTS FOR (u:User)  REQUIRE u.name IS UNIQUE;
CREATE CONSTRAINT movie_title IF NOT EXISTS FOR (m:Movie) REQUIRE m.title IS UNIQUE;

LOAD CSV WITH HEADERS FROM 'file:///likes.csv' AS row
MERGE (u:User {name: row.user})
MERGE (m:Movie {title: row.movie})
MERGE (u)-[:LIKES]->(m);

Merge the nodes first and then the relationship, because MERGE on a whole pattern creates the entire pattern, including new copies of the nodes, if any piece is missing. See Cypher: MERGE, SET, DELETE and constraints.

Exercise 8: choose between a property and a relationship

Each movie has a genre. You need "all movies in the same genre as Alien" often and also "genres with the most movies". Should genre be a property or a node?

Solution. Make it a node: (:Movie)-[:IN_GENRE]->(:Genre). Then "same genre" is a short traversal, "genres by popularity" is count() grouped by a Genre node, and a genre can have its own properties and relationships (a parent genre, for instance). A property is fine when you only display the value and never traverse through it. Graph modeling is covered in more depth in Graph data modeling.

How to keep practicing

  • Redo exercise 1 for a different domain (support tickets, delivery tracking) and write the query before the table.
  • Extend the key-rule checker with a rule for IN and see which real Cassandra errors you can reproduce on a local node.
  • Add a BLOCKS relationship to the follow graph and write a suggestion query that skips blocked users.
  • Time yourself: a good interview answer to any of these takes about three minutes.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. A table has PRIMARY KEY ((user_id), placed, order_id). Which query is accepted without ALLOW FILTERING?

    The partition key is supplied with = and one range is placed on the first clustering column. The others skip a column, omit the partition key or filter on a non-key column.
  2. 2. With RF=3, what do QUORUM writes and QUORUM reads guarantee together?

    Quorum is 2 of 3. Any read set of 2 overlaps any write set of 2, so one replica in the read has the latest value.
  3. 3. Why use OPTIONAL MATCH in the 'who follows nobody' query?

    A plain MATCH removes rows without a match. OPTIONAL MATCH keeps the row with nulls, and count() of nulls is 0.
  4. 4. In the repeatable import, why MERGE the nodes before the relationship?

    Merging nodes individually (backed by uniqueness constraints) ensures that only the missing relationship is created.

Summary

  • Model Cassandra tables from the query: partition key first, then clustering columns, then buckets for growth.
  • CQL accepts a query only when the partition key is fully restricted and clustering restrictions have no gaps and at most one range.
  • Quorum math (R + W > RF) tells you what a read is guaranteed to see.
  • Friend suggestions are two-hop patterns with a filter and a distinct count; OPTIONAL MATCH keeps unmatched rows.
  • Repeatable graph imports use MERGE on nodes and relationships with uniqueness constraints.

Next: Cassandra, Neo4j, or something else?.