PuppyGraph MovieLens 32M Implementation

Schema Diagram and Graph Statistics

Node Statistics

Node Label	Count
Genre	20
Movie	87,585
User	200,948

Edge Statistics

Relationship	Count
(:Movie)-[:HAS_GENRE]-(:Genre)	154,170
(:User)-[:RATED]-(:Movie)	32,000,204
(:User)-[:TAGGED]-(:Movie)	2,000,072

Data Loading

Overview

To preprocess the MovieLens ML-32M dataset and prepare it for ingestion into PuppyGraph, we will use the delta tables created when we work with this dataset of GraphFrames. We stored the delta tables on HDFS and we simply need to read them into PuppyGraph using a one-to-one table to label matching.

Mapping Nodes

For Movie, see the following picture for mapping attributes.

For Genre node, see the following picture for mapping attributes.

For User node, see the following picture for mapping attributes.

Mapping Edges

For HAS_GENRE edge, see the following pictures for mapping attributes.

For TAGGED edge, see the following pictures for mapping attributes.

For RATED edge, see the following pictures for mapping attributes.

Queries

Select a query below to view its logic and description.

1. Neighborhood

2. Path

3. Pattern

4. Aggregation

5. Analytics

6. Hybrid

7. Others

Q1. Movies Rated by a User

Subcategory: T1.1 One-Hop Neighbors

This query lists the movies directly connected to a selected user by outgoing RATED relationships.

cypher

MATCH (:ml32m_user {userId: 33})-[r:ml32m_rated]->(m:ml32m_movie)
RETURN m.movieId AS movieId, m.title AS title,
       r.rating AS rating, r.timestamp AS ratingTime
ORDER BY rating DESC, title;

Equivalent query in GQL standard:

gql

MATCH (u:ml32m_user {userId: $userId})-[r:ml32m_rated]->(m:ml32m_movie)
RETURN m.movieId AS movieId, m.title AS title, r.rating AS rating, r.timestamp AS ratingTime
ORDER BY rating DESC, title
LIMIT $limit;

Q2. Genres within Two Hops of a User

Subcategory: T1.2 k-Hop Neighborhood

This query follows a selected user to rated movies and then to their genres, returning distinct genres within two hops.

cypher

MATCH (:ml32m_user {userId: 33})-[:ml32m_rated]->(:ml32m_movie)-[:ml32m_has_genre]->(g:ml32m_genre)
RETURN DISTINCT g.name AS genre
ORDER BY genre;

Equivalent query in GQL standard:

gql

MATCH (:ml32m_user {userId: $userId})-[:ml32m_rated]->(:ml32m_movie)-[:ml32m_has_genre]->(g:ml32m_genre)
RETURN DISTINCT g.name AS genre
ORDER BY genre
LIMIT $limit;

Q3. Users Exactly Two Rating Hops Away

Subcategory: T1.3 Exact kth-Hop Neighbors

This query finds users exactly two RATED hops away through movies co-rated with a selected user.

cypher

MATCH (:ml32m_user {userId: 33})-[:ml32m_rated]->(:ml32m_movie)<-[:ml32m_rated]-(other:ml32m_user)
WHERE other.userId <> 33
RETURN DISTINCT other.userId AS userId
ORDER BY userId;

Equivalent query in GQL standard:

gql

MATCH (:ml32m_user {userId: $userId})-[:ml32m_rated]->(:ml32m_movie)<-[:ml32m_rated]-(other:ml32m_user)
WHERE other.userId <> $userId
RETURN DISTINCT other.userId AS userId
ORDER BY userId
LIMIT $limit;

Q4. Highly Rated One-Hop Movies

Subcategory: T1.4 Neighborhood with Property Filter

This query filters a selected user's one-hop movie neighborhood to movies with ratings at or above a threshold.

cypher

MATCH (:ml32m_user {userId: 33})-[r:ml32m_rated]->(m:ml32m_movie)
WHERE r.rating >= 4.0
RETURN m.movieId AS movieId, m.title AS title, r.rating AS rating
ORDER BY rating DESC, title;

Equivalent query in GQL standard:

gql

MATCH (u:ml32m_user {userId: $userId})-[r:ml32m_rated]->(m:ml32m_movie)
WHERE r.rating >= $minRating
RETURN m.movieId AS movieId, m.title AS title, r.rating AS rating
ORDER BY rating DESC, title
LIMIT $limit;

Q5. Filter Movies by Title Text

Subcategory: T2.1 Node Filtering (Single Node Paths)

This query filters Movie nodes by title text and movie ID without traversing relationships.

cypher

MATCH (m:ml32m_movie)
WHERE m.title CONTAINS 'Love'
  AND m.movieId >= 99
RETURN m.movieId AS movieId, m.title AS title
ORDER BY m.movieId;

Equivalent query in GQL standard:

gql

MATCH (m:ml32m_movie)
WHERE m.title CONTAINS $titleText
  AND m.movieId >= $minMovieId
RETURN m.movieId AS movieId, m.title AS title
ORDER BY m.movieId
LIMIT $limit;

Q6. Filter High-Rating Edges

Subcategory: T2.2 Edge Filtering

This query filters RATED relationships by numeric rating and returns their endpoint user and movie.

cypher

MATCH (u:ml32m_user)-[r:ml32m_rated]->(m:ml32m_movie)
WHERE r.rating >= 4.0
RETURN u.userId AS userId, m.movieId AS movieId,
       m.title AS title, r.rating AS rating
ORDER BY rating DESC;

Equivalent query in GQL standard:

gql

MATCH (u:ml32m_user)-[r:ml32m_rated]->(m:ml32m_movie)
WHERE r.rating >= $minRating
RETURN u.userId AS userId, m.movieId AS movieId, m.title AS title, r.rating AS rating
ORDER BY rating DESC
LIMIT $limit;

Q7. Check Whether a User Can Reach a Movie

Subcategory: T2.3 Reachability between Two Nodes

This query tests whether a selected user can reach a selected movie within three undirected RATED hops.

cypher

// Aggregation over zero matches still returns one row, so this preserves both
// reachable=true and reachable=false without Neo4j's EXISTS subquery syntax.
MATCH p = (:ml32m_user {userId: 33})-[:ml32m_rated*1..3]-(:ml32m_movie {movieId: 66})
RETURN count(p) > 0 AS reachable;

Equivalent query in GQL standard:

gql

MATCH p = ANY (:ml32m_user {userId: $userId})-[:ml32m_rated]-{1,3}(:ml32m_movie {movieId: $movieId})
RETURN true AS reachable;

Q8. Shortest Co-Rating Path to a Movie

Subcategory: T2.4 Shortest Path

This query finds the shortest undirected RATED path between a selected user and movie.

cypher

MATCH p = shortestPath(
  (:ml32m_user {userId: 33})-[:ml32m_rated*1..4]-(:ml32m_movie {movieId: 66})
)
RETURN p, length(p) AS hopCount;

Equivalent query in GQL standard:

gql

MATCH p = SHORTEST (:ml32m_user {userId: $userId})-[:ml32m_rated]-{1,4}(:ml32m_movie {movieId: $movieId})
RETURN p, length(p) AS hopCount;

Q9. Simple Co-Rating Paths up to Three Hops

Subcategory: T2.5 All Simple Paths up to Length k

This query enumerates acyclic RATED paths between a selected user and movie up to three hops.

cypher

// PuppyGraph does not support single(...) iterable predicates. Compare each
// bounded path position directly to enforce node uniqueness.
MATCH p = (:ml32m_user {userId: 33})-[:ml32m_rated*1..3]-(:ml32m_movie {movieId: 66})
WHERE length(p) = 1
   OR (length(p) = 2
       AND nodes(p)[0] <> nodes(p)[1]
       AND nodes(p)[0] <> nodes(p)[2]
       AND nodes(p)[1] <> nodes(p)[2])
   OR (length(p) = 3
       AND nodes(p)[0] <> nodes(p)[1]
       AND nodes(p)[0] <> nodes(p)[2]
       AND nodes(p)[0] <> nodes(p)[3]
       AND nodes(p)[1] <> nodes(p)[2]
       AND nodes(p)[1] <> nodes(p)[3]
       AND nodes(p)[2] <> nodes(p)[3])
RETURN p, length(p) AS hopCount
ORDER BY hopCount;

Equivalent query in GQL standard:

gql

MATCH p = ACYCLIC (:ml32m_user {userId: $userId})-[:ml32m_rated]-{1,3}(:ml32m_movie {movieId: $movieId})
RETURN p, length(p) AS hopCount
ORDER BY hopCount
LIMIT $limit;

Q10. Top Weighted Co-Rating Paths

Subcategory: T2.6 top-K Weighted Paths

This query ranks bounded RATED paths by the sum of rating values along each path.

cypher

MATCH p = (:ml32m_user {userId: 33})-[:ml32m_rated*1..3]-(:ml32m_movie {movieId: 66})
WHERE length(p) = 1
   OR (length(p) = 2
       AND nodes(p)[0] <> nodes(p)[1]
       AND nodes(p)[0] <> nodes(p)[2]
       AND nodes(p)[1] <> nodes(p)[2])
   OR (length(p) = 3
       AND nodes(p)[0] <> nodes(p)[1]
       AND nodes(p)[0] <> nodes(p)[2]
       AND nodes(p)[0] <> nodes(p)[3]
       AND nodes(p)[1] <> nodes(p)[2]
       AND nodes(p)[1] <> nodes(p)[3]
       AND nodes(p)[2] <> nodes(p)[3])
WITH p, reduce(total = 0.0, r IN relationships(p) |
               total + coalesce(r.rating, 0.0)) AS ratingWeight
RETURN p, length(p) AS hopCount, ratingWeight
ORDER BY ratingWeight DESC;

Equivalent query in GQL standard:

gql

MATCH p = ACYCLIC (:ml32m_user {userId: $userId})-[:ml32m_rated]-{1,3}(:ml32m_movie {movieId: $movieId})
WITH p, reduce(total = 0.0, r IN relationships(p) | total + coalesce(r.rating, 0.0)) AS ratingWeight
RETURN p, length(p) AS hopCount, ratingWeight
ORDER BY ratingWeight DESC
LIMIT $limit;

Q11. Three-Movie Triangle by Shared Genres

Subcategory: T3.1 Triangle Detection

This query finds three movies where every pair shares at least one genre through HAS_GENRE relationships.

cypher

MATCH (a:ml32m_movie)-[:ml32m_has_genre]->(:ml32m_genre)<-[:ml32m_has_genre]-(b:ml32m_movie),
      (b)-[:ml32m_has_genre]->(:ml32m_genre)<-[:ml32m_has_genre]-(c:ml32m_movie),
      (c)-[:ml32m_has_genre]->(:ml32m_genre)<-[:ml32m_has_genre]-(a)
WHERE a.movieId < b.movieId AND b.movieId < c.movieId
RETURN DISTINCT a.title AS movieA, b.title AS movieB, c.title AS movieC;

Equivalent query in GQL standard:

gql

MATCH (a:ml32m_movie)-[:ml32m_has_genre]->(:ml32m_genre)<-[:ml32m_has_genre]-(b:ml32m_movie)
MATCH (b)-[:ml32m_has_genre]->(:ml32m_genre)<-[:ml32m_has_genre]-(c:ml32m_movie)
MATCH (c)-[:ml32m_has_genre]->(:ml32m_genre)<-[:ml32m_has_genre]-(a)
WHERE a.movieId < b.movieId AND b.movieId < c.movieId
RETURN DISTINCT a.title AS movieA, b.title AS movieB, c.title AS movieC
LIMIT $limit;

Q12. Four-Movie Square by Shared Genres

Subcategory: T3.2 Square Detection

This query finds four movies connected in a cycle where each adjacent pair shares a genre.

cypher

MATCH (a:ml32m_movie)-[:ml32m_has_genre]->(:ml32m_genre)<-[:ml32m_has_genre]-(b:ml32m_movie),
      (b)-[:ml32m_has_genre]->(:ml32m_genre)<-[:ml32m_has_genre]-(c:ml32m_movie),
      (c)-[:ml32m_has_genre]->(:ml32m_genre)<-[:ml32m_has_genre]-(d:ml32m_movie),
      (d)-[:ml32m_has_genre]->(:ml32m_genre)<-[:ml32m_has_genre]-(a)
WHERE a.movieId < b.movieId
  AND a.movieId < c.movieId
  AND a.movieId < d.movieId
  AND b <> c AND b <> d AND c <> d
RETURN DISTINCT a.title AS movieA, b.title AS movieB,
       c.title AS movieC, d.title AS movieD;

Equivalent query in GQL standard:

gql

MATCH (a:ml32m_movie)-[:ml32m_has_genre]->(:ml32m_genre)<-[:ml32m_has_genre]-(b:ml32m_movie)
MATCH (b)-[:ml32m_has_genre]->(:ml32m_genre)<-[:ml32m_has_genre]-(c:ml32m_movie)
MATCH (c)-[:ml32m_has_genre]->(:ml32m_genre)<-[:ml32m_has_genre]-(d:ml32m_movie)
MATCH (d)-[:ml32m_has_genre]->(:ml32m_genre)<-[:ml32m_has_genre]-(a)
WHERE a.movieId < b.movieId AND a.movieId < c.movieId AND a.movieId < d.movieId
  AND b <> c AND b <> d AND c <> d
RETURN DISTINCT a.title AS movieA, b.title AS movieB, c.title AS movieC, d.title AS movieD
LIMIT $limit;

Q13. Users with Many Rated Movies

Subcategory: T3.3 Star Pattern Query

This query finds user-centered stars by counting how many distinct movies each user rated.

cypher

MATCH (u:ml32m_user)-[:ml32m_rated]->(m:ml32m_movie)
WITH u, count(DISTINCT m) AS ratedMovieCount
WHERE ratedMovieCount >= 10
RETURN u.userId AS userId, ratedMovieCount
ORDER BY ratedMovieCount DESC;

Equivalent query in GQL standard:

gql

MATCH (u:ml32m_user)-[:ml32m_rated]->(m:ml32m_movie)
WITH u, count(DISTINCT m) AS ratedMovieCount
WHERE ratedMovieCount >= $minDegree
RETURN u.userId AS userId, ratedMovieCount
ORDER BY ratedMovieCount DESC
LIMIT $limit;

Q14. Four-Movie Clique by Shared Genres

Subcategory: T3.4 4-Clique

This query finds four movies where every pair shares at least one genre, forming a clique in the derived movie-genre similarity graph.

cypher

MATCH (a:ml32m_movie)-[:ml32m_has_genre]->(:ml32m_genre)<-[:ml32m_has_genre]-(b:ml32m_movie),
      (a)-[:ml32m_has_genre]->(:ml32m_genre)<-[:ml32m_has_genre]-(c:ml32m_movie),
      (a)-[:ml32m_has_genre]->(:ml32m_genre)<-[:ml32m_has_genre]-(d:ml32m_movie),
      (b)-[:ml32m_has_genre]->(:ml32m_genre)<-[:ml32m_has_genre]-(c),
      (b)-[:ml32m_has_genre]->(:ml32m_genre)<-[:ml32m_has_genre]-(d),
      (c)-[:ml32m_has_genre]->(:ml32m_genre)<-[:ml32m_has_genre]-(d)
WHERE a.movieId < b.movieId
  AND b.movieId < c.movieId
  AND c.movieId < d.movieId
RETURN DISTINCT a.title AS movieA, b.title AS movieB,
       c.title AS movieC, d.title AS movieD;

Equivalent query in GQL standard:

gql

MATCH (a:ml32m_movie)-[:ml32m_has_genre]->(:ml32m_genre)<-[:ml32m_has_genre]-(b:ml32m_movie)
MATCH (a)-[:ml32m_has_genre]->(:ml32m_genre)<-[:ml32m_has_genre]-(c:ml32m_movie)
MATCH (a)-[:ml32m_has_genre]->(:ml32m_genre)<-[:ml32m_has_genre]-(d:ml32m_movie)
MATCH (b)-[:ml32m_has_genre]->(:ml32m_genre)<-[:ml32m_has_genre]-(c)
MATCH (b)-[:ml32m_has_genre]->(:ml32m_genre)<-[:ml32m_has_genre]-(d)
MATCH (c)-[:ml32m_has_genre]->(:ml32m_genre)<-[:ml32m_has_genre]-(d)
WHERE a.movieId < b.movieId AND b.movieId < c.movieId AND c.movieId < d.movieId
RETURN DISTINCT a.title AS movieA, b.title AS movieB, c.title AS movieC, d.title AS movieD
LIMIT $limit;

Q15. Users Who Rated the Same Movie

Subcategory: T3.5 Two-hop Join Pattern

This query finds pairs of users connected through a commonly rated movie.

cypher

MATCH (u1:ml32m_user)-[:ml32m_rated]->(m:ml32m_movie)<-[:ml32m_rated]-(u2:ml32m_user)
WHERE u1.userId < u2.userId
RETURN u1.userId AS userA, u2.userId AS userB,
       m.title AS sharedMovie;

Equivalent query in GQL standard:

gql

MATCH (u1:ml32m_user)-[:ml32m_rated]->(m:ml32m_movie)<-[:ml32m_rated]-(u2:ml32m_user)
WHERE u1.userId < u2.userId
RETURN u1.userId AS userA, u2.userId AS userB, m.title AS sharedMovie
LIMIT $limit;

Q16. List users and the movies they rated that belong to the specific genre "Comedy", find top-20 results in descending order of ratings

Subcategory: T3.6 Line Pattern

This query retrieves users and their comedy movie ratings, returning the top 20 highest-rated user-movie pairs.

cypher

MATCH (u:ml32m_user)-[r:ml32m_rated]->(m:ml32m_movie)-[:ml32m_has_genre]->(g:ml32m_genre {name: 'Comedy'})
RETURN u.userId AS userId, m.title AS movieTitle,
       g.name AS genre, r.rating AS rating
ORDER BY rating DESC
LIMIT 20;

Equivalent query in GQL standard:

gql

MATCH (u:ml32m_user)-[r:ml32m_rated]->(m:ml32m_movie)-[:ml32m_has_genre]->(g:ml32m_genre {name: "Comedy"})
RETURN u.userId AS userId, m.title AS movieTitle, g.name AS genre, r.rating AS rating
ORDER BY r.rating DESC
LIMIT 20

Q17. Movie Rating Degree Summary

Subcategory: T4.1 Ungrouped Node Degree Statistics by Label

This query computes global statistics over the number of RATED edges incident to each movie.

cypher

MATCH (m:ml32m_movie)
OPTIONAL MATCH (:ml32m_user)-[r:ml32m_rated]->(m)
WITH m, count(r) AS ratingDegree
RETURN count(m) AS movieCount,
       avg(ratingDegree) AS avgRatingDegree,
       min(ratingDegree) AS minRatingDegree,
       max(ratingDegree) AS maxRatingDegree;

Equivalent query in GQL standard:

gql

MATCH (m:ml32m_movie)
OPTIONAL MATCH (:ml32m_user)-[r:ml32m_rated]->(m)
WITH m, count(r) AS ratingDegree
RETURN count(m) AS movieCount,
       avg(ratingDegree) AS avgRatingDegree,
       min(ratingDegree) AS minRatingDegree,
       max(ratingDegree) AS maxRatingDegree;

Q18. Find users who have rated movies from at least 3 distinct genres, list the top-10 in descending order of the genre count

Subcategory: T4.2 Grouped Node Degree Statistics by Label

This query identifies the top 10 users who have rated movies spanning the most distinct genres (with a minimum of 3 genres).

cypher

MATCH (u:ml32m_user)-[:ml32m_rated]->(:ml32m_movie)-[:ml32m_has_genre]->(g:ml32m_genre)
WITH u, count(DISTINCT g.name) AS genreCount
WHERE genreCount >= 3
RETURN u.userId AS userId, genreCount
ORDER BY genreCount DESC
LIMIT 10;

Equivalent query in GQL standard:

gql

MATCH (u:ml32m_user)-[r:ml32m_rated]->(m:ml32m_movie)-[:ml32m_has_genre]->(g:ml32m_genre)
WITH u, count(DISTINCT g.name) AS genreCount
WHERE genreCount >= 3
RETURN u.userId AS userId, genreCount
ORDER BY genreCount DESC
LIMIT 10

Q19. Find top-20 movies with the highest number of 5-ratings, return the movies and this rating number

Subcategory: T4.2 Grouped Node Degree Statistics by Label

This query identifies and lists the top 20 movies that have received the highest count of perfect 5.0 ratings.

cypher

MATCH (:ml32m_user)-[r:ml32m_rated]->(m:ml32m_movie)
WHERE r.rating = 5.0
RETURN m.title AS movieTitle, count(*) AS fiveStarCount
ORDER BY fiveStarCount DESC
LIMIT 20;

Equivalent query in GQL standard:

gql

MATCH (:ml32m_user)-[r:ml32m_rated]->(m:ml32m_movie)
WHERE r.rating = 5.0
RETURN m.title AS movieTitle, count(*) AS fiveStarCount
ORDER BY fiveStarCount DESC
LIMIT 20

Q20. Check different rating values and their occurrence frequencies

Subcategory: T4.4 Grouped Edge Count and Property Statistics

This query aggregates all ratings in the dataset and returns each rating value along with its total occurrence frequency, sorted by rating value.

cypher

MATCH (:ml32m_user)-[r:ml32m_rated]->(:ml32m_movie)
RETURN r.rating AS rating, count(*) AS frequency
ORDER BY rating;

Equivalent query in GQL standard:

gql

MATCH (:ml32m_user)-[r:ml32m_rated]->(:ml32m_movie)
RETURN r.rating AS rating, count(*) AS frequency
ORDER BY rating

Q21. Find the top-50 most frequent tags, return the tags along with their frequencies

Subcategory: T4.4 Grouped Edge Count and Property Statistics

This query ranks and returns the top 50 most frequently used movie tags in descending order of their frequency.

cypher

MATCH (:ml32m_user)-[t:ml32m_tagged]->(:ml32m_movie)
WHERE t.tag IS NOT NULL
RETURN t.tag AS tag, count(*) AS frequency
ORDER BY frequency DESC
LIMIT 50;

Equivalent query in GQL standard:

gql

MATCH (:User)-[t:ml32m_tagged]->(:Movie)
WHERE t.tag IS NOT NULL
RETURN t.tag AS tag, count(*) AS frequency
ORDER BY frequency DESC
LIMIT 50

Q22. User Rating Path Counts by Length

Subcategory: T4.5 Path Counts by Length

This query counts bounded RATED paths from a selected user grouped by path length.

cypher

MATCH p = (:ml32m_user {userId: 33})-[:ml32m_rated*1..3]-(target)
RETURN length(p) AS pathLength,
       count(p) AS pathCount,
       count(DISTINCT target) AS distinctTargetCount
ORDER BY pathLength;

Equivalent query in GQL standard:

gql

MATCH p = (:ml32m_user {userId: $userId})-[:ml32m_rated]-{1,3}(target)
RETURN length(p) AS pathLength,
       count(p) AS pathCount,
       count(DISTINCT target) AS distinctTargetCount
ORDER BY pathLength;

Q23. User Rating Path Counts by Length and Genre

Subcategory: T4.6 Grouped Path Counts by Length and Endpoint Type

This query groups bounded paths from a selected user by path length and by genre of reached movie endpoints.

cypher

MATCH p = (:ml32m_user {userId: 33})-[:ml32m_rated*1..3]-(m:ml32m_movie)
          -[:ml32m_has_genre]->(g:ml32m_genre)
RETURN length(p) - 1 AS pathLength,
       g.name AS genre,
       count(p) AS pathCount,
       count(DISTINCT m) AS distinctMovieCount
ORDER BY pathLength, pathCount DESC;

Equivalent query in GQL standard:

gql

MATCH p = (:ml32m_user {userId: $userId})-[:ml32m_rated]-{1,3}(m:ml32m_movie)-[:ml32m_has_genre]->(g:ml32m_genre)
RETURN length(p) AS pathLength,
       g.name AS genre,
       count(p) AS pathCount,
       count(DISTINCT m) AS distinctMovieCount
ORDER BY pathLength, pathCount DESC
LIMIT $limit;

Q24. Find the title of the movies that belong to multiple genres, e.g., "Comedy" and "Romance"

Subcategory: T4.7 Union and Intersection

This query finds and lists the titles of the first 10 movies that are classified under both the Comedy and Romance genres.

cypher

MATCH (m:ml32m_movie)-[:ml32m_has_genre]->(:ml32m_genre {name: 'Comedy'}),
      (m)-[:ml32m_has_genre]->(:ml32m_genre {name: 'Romance'})
RETURN m.title AS movieTitle
LIMIT 10;

Equivalent query in GQL standard:

gql

MATCH (m:ml32m_movie)-[:ml32m_has_genre]->(g1:ml32m_genre {name: "Comedy"})
MATCH (m)-[:ml32m_has_genre]->(g2:ml32m_genre {name: "Romance"})
RETURN m.title AS movieTitle
LIMIT 10

Q25. Find users who have rated movies in both "Action" and "Romance" genres, list 10 userIds

Subcategory: T4.7 Union and Intersection

This query identifies and lists up to 10 unique user IDs who have rated at least one Action movie and at least one Romance movie.

cypher

MATCH (u:ml32m_user)-[:ml32m_rated]->(:ml32m_movie)-[:ml32m_has_genre]->(:ml32m_genre {name: 'Action'}),
      (u)-[:ml32m_rated]->(:ml32m_movie)-[:ml32m_has_genre]->(:ml32m_genre {name: 'Romance'})
RETURN DISTINCT u.userId AS userId
LIMIT 10;

Equivalent query in GQL standard:

gql

MATCH (u:ml32m_user)-[r:ml32m_rated]->(m1:ml32m_movie)-[:ml32m_has_genre]->(g1:ml32m_genre {name: "Action"})
MATCH (u)-[r:ml32m_rated]->(m2:ml32m_movie)-[:ml32m_has_genre]->(g2:ml32m_genre {name: "Romance"})
RETURN DISTINCT u.userId AS userId
LIMIT 10

Q26. Degree Centrality on the User-Movie Rating Graph

Subcategory: T5.1 Degree Centrality

This query projects the bipartite User-Movie rating graph and ranks nodes by degree centrality.

cypher

// This direct openCypher form includes zero-degree User/Movie vertices and
// counts RATED relationships without a Neo4j GDS projection.
MATCH (n)
WHERE n:ml32m_user OR n:ml32m_movie
OPTIONAL MATCH (n)-[r:ml32m_rated]-()
WITH n, count(r) AS score
RETURN labels(n) AS labels,
       coalesce(n.userId, n.movieId) AS id,
       n.title AS title,
       score
ORDER BY score DESC;

Equivalent query in GQL standard:

gql

MATCH (n:ml32m_user|ml32m_movie)
OPTIONAL MATCH (n)-[r:ml32m_rated]-()
WITH n, count(r) AS score
RETURN coalesce(n.userId, n.movieId) AS id,
       n.title AS title,
       score
ORDER BY score DESC
LIMIT $limit;

Q27. PageRank on the User-Movie Rating Graph

Subcategory: T5.2 PageRank

This query runs PageRank over the projected User-Movie rating graph and returns the highest scoring nodes.

cypher

// PuppyGraph returns a composite vertex ID such as User[33] or Movie[66].
// Its PageRank procedure uses the stored direction of RATED; PuppyGraph 1.3.0
// does not expose Neo4j GDS's orientation:'UNDIRECTED' projection option.
CALL algo.paral.pagerank({
  labels: ['ml32m_user', 'ml32m_movie'],
  relationshipTypes: ['ml32m_rated'],
  maxIterations: 20,
  dampingFactor: 0.85
}) YIELD id, score
RETURN id, score
ORDER BY score DESC;

Note: Cannot be expressed in standard GQL because the GQL standard covers graph pattern matching and data manipulation only; it defines no graph algorithm procedures such as PageRank.

Q28. Weakly Connected Components of Ratings

Subcategory: T5.3 Connected Components / Weakly Connected Components

This query finds weakly connected components in the undirected User-Movie rating graph.

cypher

// WCC ignores the stored relationship direction by definition.
CALL algo.wcc({
  labels: ['ml32m_user', 'ml32m_movie'],
  relationshipTypes: ['ml32m_rated'],
  maxIterations: 20
}) YIELD id, componentId
RETURN componentId, count(*) AS componentSize
ORDER BY componentSize DESC;

Note: Cannot be expressed in standard GQL because the GQL standard covers graph pattern matching and data manipulation only; it defines no graph algorithm procedures such as connected components.

Q29. Strongly Connected Components of Directed Ratings

Subcategory: T5.4 Strongly Connected Components

This query treats RATED edges as directed from users to movies and reports strongly connected components.

Skipped: The schema contains no undirected edges, so SCC is not meaningful for this workload. Therefore, Q29 is skipped.

Q30. Louvain Communities in the Rating Graph

Subcategory: T5.5 Community Detection

This query detects communities in the undirected User-Movie rating graph using Louvain.

cypher

CALL algo.louvain({
  labels: ['ml32m_user', 'ml32m_movie'],
  relationshipTypes: ['ml32m_rated'],
  maxIterations: 30,
  maxLevels: 1
}) YIELD id, communityId
RETURN communityId, count(*) AS communitySize
ORDER BY communitySize DESC;

Note: Cannot be expressed in standard GQL because the GQL standard covers graph pattern matching and data manipulation only; it defines no graph algorithm procedures such as community detection (Louvain).

Q31. Find movies that span at least 3 genres and have at least 50 user ratings, return top-10 movies in descending order of their rating count, along with the list of genres and the rating count

Subcategory: T4.2 Grouped Node Degree Statistics by Label; T4.3 Ungrouped Edge Count and Property Statistics

This query retrieves the top 10 most rated movies that belong to at least 3 genres and have received at least 50 user ratings, showing their titles, genres, and rating counts.

cypher

MATCH (m:ml32m_movie)-[e:ml32m_has_genre|ml32m_rated]-(n)
WITH m.movieId AS movieId, m.title AS movie,
     collect(DISTINCT CASE
       WHEN type(e) = 'ml32m_has_genre' THEN 'G:' + n.name
       ELSE 'R:' + toString(n.userId)
     END) AS facts
WITH movieId, movie,
     [x IN facts WHERE x STARTS WITH 'G:' | substring(x, 2)] AS genres,
     size([x IN facts WHERE x STARTS WITH 'R:']) AS ratingCount
WHERE ratingCount >= 50
  AND size(genres) >= 3
RETURN movie, genres, ratingCount
ORDER BY ratingCount DESC
LIMIT 10;

Equivalent query in GQL standard:

gql

MATCH (m:ml32m_movie)-[:ml32m_has_genre]->(g:ml32m_genre)
MATCH (u:ml32m_user)-[r:ml32m_rated]->(m)
WITH m, collect(DISTINCT g.name) AS genres, count(r) AS ratingCount
WHERE size(genres) >= 3 AND ratingCount >= 50
RETURN m.title AS movie, genres, ratingCount
ORDER BY ratingCount DESC
LIMIT 10

Q32. Find top-20 movies with the highest number of genres + tags, return this number, list of their genres, and list of their tags

Subcategory: Other

This query finds the top 20 movies with the largest combined count of unique genres and tags, returning the total count along with their genre and tag lists.

cypher

MATCH (m:ml32m_movie)
OPTIONAL MATCH (m)-[e:ml32m_has_genre|ml32m_tagged]-(n)
WITH m.movieId AS movieId, m.title AS movieTitle,
     collect(DISTINCT CASE
       WHEN type(e) = 'ml32m_has_genre' THEN 'G:' + n.name
       ELSE 'T:' + e.tag
     END) AS facts
WITH movieId, movieTitle,
     [x IN facts WHERE x STARTS WITH 'G:' | substring(x, 2)] AS genres,
     [x IN facts WHERE x STARTS WITH 'T:' | substring(x, 2)] AS tags
WITH movieId, movieTitle, genres, tags,
     size(genres) + size(tags) AS total
RETURN movieTitle, total, genres, tags
ORDER BY total DESC
LIMIT 20;

Equivalent query in GQL standard:

gql

MATCH (m:ml32m_movie)
OPTIONAL MATCH (m)-[:ml32m_has_genre]->(g:ml32m_genre)
OPTIONAL MATCH (:ml32m_user)-[t:ml32m_tagged]->(m)
WITH m,
     collect(DISTINCT g.name) AS genres,
     collect(DISTINCT t.tag) AS tags,
     size(collect(DISTINCT g.name)) + size(collect(DISTINCT t.tag)) AS total
ORDER BY total DESC
LIMIT 20
RETURN m.title AS movieTitle, total, genres, tags

Q33. Find (user, movie) pairs where the movie is first rated by the user, and then tagged by the user, return 10 such user-movie pairs with the latest timestamps, along with the rating and the list of tags (tagged after the rating)

Subcategory: Other

This query retrieves the top 10 user-movie pairs where the user rated a movie before tagging it, sorted by the rating timestamp.

cypher

MATCH (u:ml32m_user)-[r:ml32m_rated]->(m:ml32m_movie)<-[t:ml32m_tagged]-(u)
WHERE r.timestamp < t.timestamp
WITH u.userId AS userId, m.title AS movieTitle,
     r.rating AS rating, r.timestamp AS ratingTime,
     collect(DISTINCT t.tag) AS tags
RETURN userId, movieTitle, rating, tags
ORDER BY ratingTime DESC
LIMIT 10;

Equivalent query in GQL standard:

gql

MATCH (u:ml32m_user)-[r:ml32m_rated]->(m:ml32m_movie)<-[t:ml32m_tagged]-(u)
WHERE r.timestamp < t.timestamp
WITH u.userId AS userId, m.title AS movieTitle, r.rating AS rating, r.timestamp AS ratingTime,
     collect(DISTINCT t.tag) AS tags
RETURN userId, movieTitle, rating, tags
ORDER BY ratingTime DESC
LIMIT 10