PuppyGraph MovieLens Tag Genome Implementation

Schema Diagram and Graph Statistics

Node Statistics

Node Label	Count
Actor	122,974
Director	33,660
Movie	84,661
Review	2,623,824
Survey	58,903
Tag	1,094
User	200,948

Edge Statistics

Relationship	Count
(:Survey)-[:ABOUT_MOVIE]-(:Movie)	58,903
(:Survey)-[:ABOUT_TAG]-(:Tag)	58,903
(:Actor)-[:ACTED_IN]-(:Movie)	371,629
(:User)-[:ANSWERED]-(:Survey)	58,903
(:Director)-[:DIRECTED]-(:Movie)	81,511
(:Movie)-[:HAS_REVIEW]-(:Review)	2,623,824
(:Movie)-[:HAS_TAG]-(:Tag)	212,092
(:User)-[:RATED]-(:Movie)	18,874,784

Data Loading

Overview

To preprocess the MovieLens Tag Genome 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.

Now that we have all the Delta tables we need, we can go to PuppyGraph GUI to map nodes and edges from these tables.

Mapping Nodes

For Movie, see the following picture for mapping attributes. The attributes are item_id, title, avgRating, imdbId , directedBy, starring.

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

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

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

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

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

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

Mapping Edges

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

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

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

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

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

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

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

For ACTED_IN 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 Selected User

Subcategory: T1.1 One-Hop Neighbors

This query lists the movies directly connected to a selected user through outgoing RATED edges.

cypher

MATCH (u:mlgenome_user {user_id: 997206})-[r:mlgenome_rated]->(m:mlgenome_movie)
RETURN m.item_id AS MovieID, m.title AS Movie, r.rating AS Rating
ORDER BY Rating DESC;

Equivalent query in GQL standard:

gql

MATCH (u:mlgenome_user {user_id: 1})-[r:mlgenome_rated]->(m:mlgenome_movie)
RETURN m.item_id AS MovieID, m.title AS Movie, r.rating AS Rating
ORDER BY Rating DESC
LIMIT 20

Q2. Two-Hop Rating Neighborhood of a User

Subcategory: T1.2 k-Hop Neighborhood

This query finds distinct users reachable within two RATED hops from a selected user through shared movies.

cypher

MATCH p = (u:mlgenome_user {user_id: 997206})-[:mlgenome_rated*1..2]-(n)
WHERE n <> u
RETURN DISTINCT labels(n) AS NodeLabels,
       coalesce(n.title, toString(n.user_id)) AS Neighbor,
       length(p) AS HopCount
ORDER BY HopCount ASC, Neighbor;

Equivalent query in GQL standard:

gql

MATCH p = (u:mlgenome_user {user_id: 1})-[:mlgenome_rated]-{1,2}(n)
WHERE n != u
RETURN DISTINCT labels(n) AS NodeLabels,
       coalesce(n.title, toString(n.user_id)) AS Neighbor,
       length(p) AS HopCount
ORDER BY HopCount ASC, Neighbor
LIMIT 50

Q3. Users Exactly Two Hops Away Through Shared Movies

Subcategory: T1.3 Exact kth-Hop Neighbors

This query returns users exactly two RATED hops away from a selected user, meaning users who rated at least one same movie.

cypher

MATCH (u:mlgenome_user {user_id: 997206})-[:mlgenome_rated]->(m:mlgenome_movie)<-[:mlgenome_rated]-(other:mlgenome_user)
WHERE other <> u
RETURN other.user_id AS SimilarUser, count(DISTINCT m) AS SharedMovies
ORDER BY SharedMovies DESC;

Equivalent query in GQL standard:

gql

MATCH (u:mlgenome_user {user_id: 1})-[:mlgenome_rated]->(m:mlgenome_movie)<-[:mlgenome_rated]-(other:mlgenome_user)
WHERE other != u
RETURN other.user_id AS SimilarUser, count(DISTINCT m) AS SharedMovies
ORDER BY SharedMovies DESC
LIMIT 20

Q4. High-Rated One-Hop Movie Neighborhood

Subcategory: T1.4 Neighborhood with Property Filter

This query searches a selected user's immediate movie neighborhood while filtering on both the RATED edge value and the movie's average rating.

cypher

MATCH (u:mlgenome_user {user_id: 997206})-[r:mlgenome_rated]->(m:mlgenome_movie)
WHERE r.rating >= 4.0 AND m.avgRating >= 4.0
RETURN m.item_id AS MovieID, m.title AS Movie,
       r.rating AS UserRating, m.avgRating AS AverageRating
ORDER BY UserRating DESC, AverageRating DESC;

Equivalent query in GQL standard:

gql

MATCH (u:mlgenome_user {user_id: 1})-[r:mlgenome_rated]->(m:mlgenome_movie)
WHERE r.rating >= 4.0 AND m.avgRating >= 4.0
RETURN m.item_id AS MovieID, m.title AS Movie, r.rating AS UserRating, m.avgRating AS AverageRating
ORDER BY UserRating DESC, AverageRating DESC
LIMIT 20

Q5. Users Who Did NOT Participate in Any Survey

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

This query identifies users who have not participated in any tag genome surveys.

cypher

MATCH (u:mlgenome_user)
OPTIONAL MATCH (u)-[answered:mlgenome_answered]->(:mlgenome_survey)
WITH u, count(answered) AS SurveyCount
WHERE SurveyCount = 0
RETURN u.user_id AS UserID;

Equivalent query in GQL standard:

gql

MATCH (u:mlgenome_user)
WHERE NOT (u)-[:mlgenome_answered]->(:mlgenome_survey)
RETURN u.user_id AS UserID
LIMIT 20

Q6. High-Count Movie Tag Edges

Subcategory: T2.2 Edge Filtering

This query filters HAS_TAG edges by their count property to find strong movie-tag associations.

cypher

MATCH (m:mlgenome_movie)-[ht:mlgenome_has_tag]->(t:mlgenome_tag)
WHERE ht.count >= 20
RETURN m.title AS Movie, t.tag AS Tag, ht.count AS TagCount
ORDER BY TagCount DESC;

Equivalent query in GQL standard:

gql

MATCH (m:mlgenome_movie)-[ht:mlgenome_has_tag]->(t:mlgenome_tag)
WHERE ht.count >= 20
RETURN m.title AS Movie, t.tag AS Tag, ht.count AS TagCount
ORDER BY TagCount DESC
LIMIT 20

Q7. User Reachability to a Tag Through Rated Movies

Subcategory: T2.3 Reachability between Two Nodes

This query checks whether a selected user can reach a selected tag through movies they rated.

cypher

MATCH (u:mlgenome_user {user_id: 997206})
MATCH (t:mlgenome_tag {tag_id: 22})
WITH u, t
LIMIT 1
OPTIONAL MATCH p = (u)-[:mlgenome_rated]->(:mlgenome_movie)-[:mlgenome_has_tag]->(t)
RETURN u.user_id AS UserID, t.tag AS Tag, count(p) > 0 AS Reachable;

Equivalent query in GQL standard:

gql

MATCH (u:mlgenome_user {user_id: 1})
MATCH (t:mlgenome_tag)
WITH u, t
LIMIT 1
OPTIONAL MATCH p = (u)-[:mlgenome_rated]->(:mlgenome_movie)-[:mlgenome_has_tag]->(t)
RETURN u.user_id AS UserID, t.tag AS Tag, count(p) > 0 AS Reachable

Q8. Shortest User-to-Tag Explanation Path

Subcategory: T2.4 Shortest Path

This query finds the shortest path from a user to a tag over ratings and movie-tag relationships.

cypher

MATCH (u:mlgenome_user {user_id: 997206})
MATCH (t:mlgenome_tag {tag_id: 22})
WITH u, t
LIMIT 1
MATCH p = shortestPath((u)-[:mlgenome_rated|mlgenome_has_tag*1..4]-(t))
RETURN [n IN nodes(p) |
          coalesce(n.title, n.tag, toString(n.user_id))] AS Path,
       length(p) AS Hops;

Equivalent query in GQL standard:

gql

MATCH p = SHORTEST (u:mlgenome_user {user_id: 1})-[:mlgenome_rated|mlgenome_has_tag]-{1,4}(t:mlgenome_tag)
RETURN p

Q9. Simple Co-Rating Paths Between Users

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

This query enumerates simple RATED paths up to four hops between one user and another user in the rating graph.

cypher

// PuppyGraph does not support single(...) iterable predicates. Since the path
// is bounded at three hops, compare every possible node position explicitly.
MATCH (source:mlgenome_user {user_id: 997206})
MATCH (target:mlgenome_user)
WHERE target <> source
WITH source, target
LIMIT 1
MATCH p = (source)-[:mlgenome_rated*1..3]-(target)
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 [n IN nodes(p) |
          coalesce(n.title, toString(n.user_id))] AS Path,
       length(p) AS Hops;

Equivalent query in GQL standard:

gql

MATCH p = ACYCLIC (source:mlgenome_user {user_id: 1})-[:mlgenome_rated]-{1,4}(target:mlgenome_user)
WHERE target != source
RETURN p
LIMIT 20

Q10. User-Movie Co-Rating Squares

Subcategory: T3.2 Square Detection

This query finds two users who both rated the same pair of movies, forming a four-node square in the user-movie rating graph.

cypher

MATCH (u1:mlgenome_user)-[:mlgenome_rated]->(m1:mlgenome_movie)<-[:mlgenome_rated]-(u2:mlgenome_user)-[:mlgenome_rated]->(m2:mlgenome_movie)<-[:mlgenome_rated]-(u1)
WHERE u1.user_id < u2.user_id AND m1.item_id < m2.item_id
RETURN u1.user_id AS User1, u2.user_id AS User2,
       m1.title AS Movie1, m2.title AS Movie2;

Equivalent query in GQL standard:

gql

MATCH (u1:mlgenome_user)-[:mlgenome_rated]->(m1:mlgenome_movie)<-[:mlgenome_rated]-(u2:mlgenome_user)-[:mlgenome_rated]->(m2:mlgenome_movie)<-[:mlgenome_rated]-(u1)
WHERE u1.user_id < u2.user_id AND m1.item_id < m2.item_id
RETURN u1.user_id AS User1, u2.user_id AS User2, m1.title AS Movie1, m2.title AS Movie2
LIMIT 20

Q11. Movie Star Pattern Across Cast, Tags, and Reviews

Subcategory: T3.3 Star Pattern Query

This query treats each movie as a star center connected to actors, tags, and reviews, then returns the busiest centers.

cypher

MATCH (m:mlgenome_movie)
OPTIONAL MATCH (a:mlgenome_actor)-[:mlgenome_acted_in]->(m)
OPTIONAL MATCH (m)-[:mlgenome_has_tag]->(t:mlgenome_tag)
OPTIONAL MATCH (m)-[:mlgenome_has_review]->(r:mlgenome_review)
WITH m, count(DISTINCT a) AS ActorCount,
        count(DISTINCT t) AS TagCount,
        count(DISTINCT r) AS ReviewCount
WHERE ActorCount >= 2 AND TagCount >= 2
RETURN m.title AS Movie, ActorCount, TagCount, ReviewCount,
       ActorCount + TagCount + ReviewCount AS StarDegree
ORDER BY StarDegree DESC;

Equivalent query in GQL standard:

gql

MATCH (m:mlgenome_movie)
OPTIONAL MATCH (a:mlgenome_actor)-[:mlgenome_acted_in]->(m)
OPTIONAL MATCH (m)-[:mlgenome_has_tag]->(t:mlgenome_tag)
OPTIONAL MATCH (m)-[:mlgenome_has_review]->(r:mlgenome_review)
WITH m, count(DISTINCT a) AS ActorCount, count(DISTINCT t) AS TagCount, count(DISTINCT r) AS ReviewCount
WHERE ActorCount >= 2 AND TagCount >= 2
RETURN m.title AS Movie, ActorCount, TagCount, ReviewCount, ActorCount + TagCount + ReviewCount AS StarDegree
ORDER BY StarDegree DESC
LIMIT 20

Q12. Four-Actor Co-Starring Cliques

Subcategory: T3.4 4-Clique

This query finds four actors who co-star in the same movie, representing a four-clique in the projected co-actor graph.

cypher

MATCH (a1:mlgenome_actor)-[:mlgenome_acted_in]->(m:mlgenome_movie)<-[:mlgenome_acted_in]-(a2:mlgenome_actor),
      (a3:mlgenome_actor)-[:mlgenome_acted_in]->(m)<-[:mlgenome_acted_in]-(a4:mlgenome_actor)
WHERE a1.name < a2.name AND a2.name < a3.name AND a3.name < a4.name
RETURN m.title AS Movie, a1.name AS Actor1, a2.name AS Actor2,
       a3.name AS Actor3, a4.name AS Actor4;

Equivalent query in GQL standard:

gql

MATCH (a1:mlgenome_actor)-[:mlgenome_acted_in]->(m:mlgenome_movie)<-[:mlgenome_acted_in]-(a2:mlgenome_actor),
      (a3:mlgenome_actor)-[:mlgenome_acted_in]->(m)<-[:mlgenome_acted_in]-(a4:mlgenome_actor)
WHERE a1.name < a2.name AND a2.name < a3.name AND a3.name < a4.name
RETURN m.title AS Movie, a1.name AS Actor1, a2.name AS Actor2, a3.name AS Actor3, a4.name AS Actor4
LIMIT 20

Q13. Most Similar Users Based on Ratings for the Same Movies (Find pairs of users with overlapping movie preferences. (Collaborative filtering foundation))

Subcategory: T3.5 Two-hop Join Pattern

This query finds pairs of users who have rated the same movies similarly (within a difference of 1 rating point) and share at least 5 such movie preferences.

cypher

MATCH (u1:mlgenome_user)-[r1:mlgenome_rated]->(m:mlgenome_movie)<-[r2:mlgenome_rated]-(u2:mlgenome_user)
WHERE u1 <> u2 AND abs(r1.rating - r2.rating) <= 1
WITH u1.user_id AS User1, u2.user_id AS User2,
     count(DISTINCT m) AS SharedMovies
WHERE SharedMovies >= 5
RETURN User1, User2, SharedMovies
ORDER BY SharedMovies DESC;

Equivalent query in GQL standard:

gql

MATCH (u1:mlgenome_user)-[r1:mlgenome_rated]->(m:mlgenome_movie)<-[r2:mlgenome_rated]-(u2:mlgenome_user)
WHERE u1 != u2 AND abs(r1.rating - r2.rating) <= 1
WITH u1.user_id AS User1, u2.user_id AS User2, count(m) AS SharedMovies
WHERE SharedMovies >= 5
RETURN User1, User2, SharedMovies
ORDER BY SharedMovies DESC
LIMIT 10

Q14. User-Survey-Movie-Tag Line Pattern

Subcategory: T3.6 Line Pattern

This query follows a four-node line from users to surveys, then to movies, and finally to tags attached to those movies.

cypher

MATCH (u:mlgenome_user)-[:mlgenome_answered]->(s:mlgenome_survey)-[:mlgenome_about_movie]->(m:mlgenome_movie)-[:mlgenome_has_tag]->(t:mlgenome_tag)
RETURN u.user_id AS UserID, s.survey_id AS SurveyID,
       m.title AS Movie, t.tag AS Tag, s.score AS SurveyScore;

Equivalent query in GQL standard:

gql

MATCH (u:mlgenome_user)-[:mlgenome_answered]->(s:mlgenome_survey)-[:mlgenome_about_movie]->(m:mlgenome_movie)-[:mlgenome_has_tag]->(t:mlgenome_tag)
RETURN u.user_id AS UserID, s.survey_id AS SurveyID, m.title AS Movie, t.tag AS Tag, s.score AS SurveyScore
LIMIT 20

Q15. Movie Degree Summary Across All Relationship Types

Subcategory: T4.1 Ungrouped Node Degree Statistics by Label

This query computes ungrouped degree statistics for Movie nodes across their adjacent relationships.

cypher

MATCH (m:mlgenome_movie)
OPTIONAL MATCH (m)-[]-(n)
WITH m, count(DISTINCT n) AS Degree
RETURN count(m) AS MovieCount,
       round(avg(Degree) * 100.0) / 100.0 AS AvgDegree,
       min(Degree) AS MinDegree,
       max(Degree) AS MaxDegree;

Equivalent query in GQL standard:

gql

MATCH (m:mlgenome_movie)
OPTIONAL MATCH (m)-[]-(n)
WITH m, count(DISTINCT n) AS Degree
RETURN count(m) AS MovieCount,
       round(avg(Degree), 2) AS AvgDegree,
       min(Degree) AS MinDegree,
       max(Degree) AS MaxDegree

Q16. Directors by Average Movie Rating (Filtering to avoid bias from 1-hit wonders, so considering movies >= 3)

Subcategory: T4.2 Grouped Node Degree Statistics by Label

This query computes the average rating and movie count for each director with at least 3 movies, sorting them to find the top 10 highest-rated directors.

cypher

MATCH (d:mlgenome_director)-[:mlgenome_directed]->(m:mlgenome_movie)
WHERE m.avgRating IS NOT NULL
WITH d.name AS Director,
     round(avg(m.avgRating) * 100.0) / 100.0 AS AvgRating,
     count(m) AS Movies
WHERE Movies >= 3
RETURN Director, AvgRating, Movies
ORDER BY AvgRating DESC;

Equivalent query in GQL standard:

gql

MATCH (d:mlgenome_director)-[:mlgenome_directed]->(m:mlgenome_movie)
WHERE m.avgRating IS NOT NULL
WITH d.name AS Director, avg(m.avgRating) AS AvgRating, count(m) AS Movies
WHERE Movies >= 3
RETURN Director, round(AvgRating, 2) AS AvgRating, Movies
ORDER BY AvgRating DESC
LIMIT 10

Q17. Actors by Number of Movies Acted In

Subcategory: T4.2 Grouped Node Degree Statistics by Label

This query calculates the total number of movies each actor has starred in and returns the top 10 most prolific actors.

cypher

MATCH (a:mlgenome_actor)-[:mlgenome_acted_in]->(m:mlgenome_movie)
RETURN a.name AS Actor, count(m) AS TotalMovies
ORDER BY TotalMovies DESC;

Equivalent query in GQL standard:

gql

MATCH (a:mlgenome_actor)-[:mlgenome_acted_in]->(m:mlgenome_movie)
RETURN a.name AS Actor, count(m) AS TotalMovies
ORDER BY TotalMovies DESC
LIMIT 10

Q18. Rating Edge Summary

Subcategory: T4.3 Ungrouped Edge Count and Property Statistics (Optional)

This query summarizes all RATED edges by count and rating-value statistics.

cypher

MATCH (:mlgenome_user)-[r:mlgenome_rated]->(:mlgenome_movie)
RETURN count(r) AS RatingCount,
       round(avg(r.rating) * 100.0) / 100.0 AS AvgRating,
       min(r.rating) AS MinRating,
       max(r.rating) AS MaxRating;

Equivalent query in GQL standard:

gql

MATCH (:mlgenome_user)-[r:mlgenome_rated]->(:mlgenome_movie)
RETURN count(r) AS RatingCount,
       round(avg(r.rating), 2) AS AvgRating,
       min(r.rating) AS MinRating,
       max(r.rating) AS MaxRating

Q19. Users Who Rate Harshly (Lowest Average Rating)

Subcategory: T4.4 Grouped Edge Count and Property Statistics

This query identifies the top 10 users with the lowest average ratings among those who have submitted at least 5 ratings, highlighting the harshest critics.

cypher

MATCH (u:mlgenome_user)-[r:mlgenome_rated]->(:mlgenome_movie)
WHERE r.rating IS NOT NULL
WITH u.user_id AS UserID, count(r) AS TotalRatings,
     round(avg(r.rating) * 100.0) / 100.0 AS AvgRating
WHERE TotalRatings >= 5
RETURN UserID, AvgRating, TotalRatings
ORDER BY AvgRating ASC;

Equivalent query in GQL standard:

gql

MATCH (u:mlgenome_user)-[r:mlgenome_rated]->(m:mlgenome_movie)
WHERE r.rating IS NOT NULL
WITH u.user_id AS UserID, count(r) AS TotalRatings, avg(r.rating) AS AvgRating
WHERE TotalRatings >= 5
RETURN UserID, round(AvgRating, 2) AS AvgRating, TotalRatings
ORDER BY AvgRating ASC
LIMIT 10

Q20. Bounded Rating Path Counts by Length

Subcategory: T4.5 Path Counts by Length

This query counts bounded RATED paths from a selected user and groups the result only by path length.

cypher

MATCH p = (:mlgenome_user {user_id: 997206})-[:mlgenome_rated*1..3]-(target)
RETURN length(p) AS PathLength,
       count(p) AS PathCount,
       count(DISTINCT target) AS DistinctTargets
ORDER BY PathLength ASC;

Equivalent query in GQL standard:

gql

MATCH p = (:mlgenome_user {user_id: 1})-[:mlgenome_rated]-{1,3}(target)
RETURN length(p) AS PathLength,
       count(p) AS PathCount,
       count(DISTINCT target) AS DistinctTargets
ORDER BY PathLength ASC

Q21. Bounded Rating Path Counts by Length and Endpoint Type

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

This query extends path-count aggregation by grouping bounded paths by both path length and endpoint label.

cypher

MATCH p = (:mlgenome_user {user_id: 997206})-[:mlgenome_rated*1..3]-(target)
WITH length(p) AS PathLength, labels(target)[0] AS EndpointType, target, p
RETURN PathLength,
       EndpointType,
       count(p) AS PathCount,
       count(DISTINCT target) AS DistinctTargets
ORDER BY PathLength ASC, PathCount DESC;

Equivalent query in GQL standard:

gql

MATCH p = (:mlgenome_user {user_id: 1})-[:mlgenome_rated]-{1,3}(target)
WITH length(p) AS PathLength, labels(target)[0] AS EndpointType, target, p
RETURN PathLength,
       EndpointType,
       count(p) AS PathCount,
       count(DISTINCT target) AS DistinctTargets
ORDER BY PathLength ASC, PathCount DESC

Q22. Directors with the Most Movies Above 4.0 Rating (Not just average, how many great movies have they made? Considered minimum of 2 movies)

Subcategory: T5.1 Degree Centrality

This query finds the top 10 directors with the most movies rated 4.0 or above, considering only directors who have at least 2 such highly-rated movies.

cypher

MATCH (d:mlgenome_director)-[:mlgenome_directed]->(m:mlgenome_movie)
WHERE m.avgRating >= 4.0
WITH d.name AS Director, count(m) AS HighRatedMovies
WHERE HighRatedMovies >= 2
RETURN Director, HighRatedMovies
ORDER BY HighRatedMovies DESC;

Equivalent query in GQL standard:

gql

MATCH (d:mlgenome_director)-[:mlgenome_directed]->(m:mlgenome_movie)
WHERE m.avgRating >= 4.0
WITH d.name AS Director, count(m) AS HighRatedMovies
WHERE HighRatedMovies >= 2
RETURN Director, HighRatedMovies
ORDER BY HighRatedMovies DESC
LIMIT 10

Q23. Top Tags by Frequency in Surveys (Which tags are evaluated most often by users?)

Subcategory: T5.1 Degree Centrality

This query counts the total number of survey evaluations for each tag and returns the top 10 most frequently evaluated tags.

cypher

MATCH (:mlgenome_survey)-[:mlgenome_about_tag]->(t:mlgenome_tag)
RETURN t.tag AS Tag, count(*) AS SurveyCount
ORDER BY SurveyCount DESC;

Equivalent query in GQL standard:

gql

MATCH (:mlgenome_survey)-[:mlgenome_about_tag]->(t:mlgenome_tag)
RETURN t.tag AS Tag, count(*) AS SurveyCount
ORDER BY SurveyCount DESC
LIMIT 10

Q24. Tags Most Frequently Scored Highly in Surveys (Which tags do users consistently agree are applicable to movies (score ≥ 4)?)

Subcategory: T5.1 Degree Centrality

This query identifies tags that users consistently score highly (survey score >= 4) and returns the top 10 highest-scoring tags.

cypher

MATCH (s:mlgenome_survey)-[:mlgenome_about_tag]->(t:mlgenome_tag)
WHERE s.score >= 4
RETURN t.tag AS Tag, count(*) AS HighScoreCount
ORDER BY HighScoreCount DESC;

Equivalent query in GQL standard:

gql

MATCH (s:mlgenome_survey)-[:mlgenome_about_tag]->(t:mlgenome_tag)
WHERE s.score >= 4
RETURN t.tag AS Tag, count(*) AS HighScoreCount
ORDER BY HighScoreCount DESC
LIMIT 10

Q25. Movies With Most Tagged Survey Feedback (Which movies have been analyzed most often in surveys?)

Subcategory: T5.1 Degree Centrality

This query finds the top 10 movies that have been evaluated the most number of times in tag genome surveys.

cypher

MATCH (s:mlgenome_survey)-[:mlgenome_about_movie]->(m:mlgenome_movie)
RETURN m.title AS Movie, count(*) AS SurveyMentions
ORDER BY SurveyMentions DESC;

Equivalent query in GQL standard:

gql

MATCH (s:mlgenome_survey)-[:mlgenome_about_movie]->(m:mlgenome_movie)
RETURN m.title AS Movie, count(*) AS SurveyMentions
ORDER BY SurveyMentions DESC
LIMIT 10

Q26. Users Who Participated in the Most Surveys

Subcategory: T5.1 Degree Centrality

This query lists the top 10 users who have participated in the highest number of tag genome surveys.

cypher

MATCH (u:mlgenome_user)-[:mlgenome_answered]->(s:mlgenome_survey)
RETURN u.user_id AS User, count(s) AS TotalSurveys
ORDER BY TotalSurveys DESC;

Equivalent query in GQL standard:

gql

MATCH (u:mlgenome_user)-[:mlgenome_answered]->(s:mlgenome_survey)
RETURN u.user_id AS User, count(s) AS TotalSurveys
ORDER BY TotalSurveys DESC
LIMIT 10

Q27. PageRank on the MovieLens Genome Rating Graph

Subcategory: T5.2 PageRank

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

cypher

// PuppyGraph's PageRank procedure runs directly on the stored edge direction.
// Its native API does not expose Neo4j GDS orientation:'UNDIRECTED'. The id
// result is PuppyGraph's composite vertex ID rather than a matched node object.
CALL algo.paral.pagerank({
  labels: ['mlgenome_user', 'mlgenome_movie'],
  relationshipTypes: ['mlgenome_rated'],
  maxIterations: 20,
  dampingFactor: 0.85
}) YIELD id, score
RETURN id AS Node, 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 the Rating Graph

Subcategory: T5.3 Connected Components / Weakly Connected Components

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

cypher

// WCC ignores the stored relationship direction by definition.
CALL algo.wcc({
  labels: ['mlgenome_user', 'mlgenome_movie'],
  relationshipTypes: ['mlgenome_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 Rating Edges

Subcategory: T5.4 Strongly Connected Components

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

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 and returns the largest communities.

cypher

// PuppyGraph's native Louvain API does not expose an orientation parameter,
// this query therefore runs directly on the stored mlgenome_rated edges.
CALL algo.louvain({
  labels: ['mlgenome_user', 'mlgenome_movie'],
  relationshipTypes: ['mlgenome_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. Best Tags per Movie by Survey and Tag_Count

Subcategory: T3.1 Triangle Detection; T4.2 Grouped Node Degree Statistics by Label; T4.4 Grouped Edge Count and Property Statistics

This query correlates tags applied to movies via direct tag counts with survey evaluations to find movie-tag pairings with the highest average consensus score.

cypher

MATCH (m:mlgenome_movie)-[ht:mlgenome_has_tag]->(t:mlgenome_tag)
MATCH (s:mlgenome_survey)-[:mlgenome_about_movie]->(m)
MATCH (s)-[:mlgenome_about_tag]->(t)
WITH m.title AS Movie, t.tag AS Tag,
     sum(ht.count) AS TagApplications,
     avg(s.score) AS AvgSurveyScore,
     count(s) AS SurveyMentions
WHERE TagApplications > 5 AND SurveyMentions >= 5
RETURN Movie, Tag, TagApplications,
       round(AvgSurveyScore * 100.0) / 100.0 AS AvgSurveyScore,
       SurveyMentions
ORDER BY AvgSurveyScore DESC, SurveyMentions DESC;

Equivalent query in GQL standard:

gql

MATCH (m:mlgenome_movie)-[ht:mlgenome_has_tag]->(t:mlgenome_tag)
MATCH (s:mlgenome_survey)-[:mlgenome_about_movie]->(m)
MATCH (s)-[:mlgenome_about_tag]->(t)
WITH m.title AS Movie, t.tag AS Tag,
    sum(ht.count) AS TagApplications,
    avg(s.score) AS AvgSurveyScore,
    count(s) AS SurveyMentions
WHERE TagApplications > 5 AND SurveyMentions >= 5
RETURN Movie, Tag, TagApplications, round(AvgSurveyScore, 2) AS AvgSurveyScore, SurveyMentions
ORDER BY AvgSurveyScore DESC, SurveyMentions DESC
LIMIT 20

Q32. Top Actor-Director Pairs by Number of Collaborations

Subcategory: T3.5 Two-hop Join Pattern; T4.2 Grouped Node Degree Statistics by Label

This query finds the top 10 actor-director pairs with the highest number of movie collaborations.

cypher

MATCH (d:mlgenome_director)-[:mlgenome_directed]->(m:mlgenome_movie)<-[:mlgenome_acted_in]-(a:mlgenome_actor)
RETURN d.name AS Director, a.name AS Actor, count(m) AS Collaborations
ORDER BY Collaborations DESC;

Equivalent query in GQL standard:

gql

MATCH (d:mlgenome_director)-[:mlgenome_directed]->(m:mlgenome_movie)<-[:mlgenome_acted_in]-(a:mlgenome_actor)
RETURN d.name AS Director, a.name AS Actor, count(m) AS Collaborations
ORDER BY Collaborations DESC
LIMIT 10

Q33. Directors Whose Movies Collect the Most Reviews

Subcategory: T3.5 Two-hop Join Pattern; T4.2 Grouped Node Degree Statistics by Label

This query finds the top 10 directors whose movies have collectively accumulated the highest number of text reviews.

cypher

MATCH (d:mlgenome_director)-[:mlgenome_directed]->(m:mlgenome_movie)-[:mlgenome_has_review]->(r:mlgenome_review)
RETURN d.name AS Director, count(r) AS TotalReviews
ORDER BY TotalReviews DESC;

Equivalent query in GQL standard:

gql

MATCH (d:mlgenome_director)-[:mlgenome_directed]->(m:mlgenome_movie)-[:mlgenome_has_review]->(r:mlgenome_review)
RETURN d.name AS Director, count(r) AS TotalReviews
ORDER BY TotalReviews DESC
LIMIT 10

Q34. Actor-Director Collaboration Quality (Which pairs consistently produce great movies?)

Subcategory: T4.2 Grouped Node Degree Statistics by Label; T4.7 Union and Intersection

This query evaluates the quality of actor-director collaborations by computing the average rating of their shared movies, returning the top 10 pairs with at least 3 collaborations.

cypher

MATCH (d:mlgenome_director)-[:mlgenome_directed]->(m:mlgenome_movie)<-[:mlgenome_acted_in]-(a:mlgenome_actor)
WHERE m.avgRating IS NOT NULL
WITH d.name AS Director, a.name AS Actor, count(m) AS Collabs,
     round(avg(m.avgRating) * 100.0) / 100.0 AS AvgRating
WHERE Collabs >= 3
RETURN Director, Actor, Collabs, AvgRating
ORDER BY AvgRating DESC;

Equivalent query in GQL standard:

gql

MATCH (d:mlgenome_director)-[:mlgenome_directed]->(m:mlgenome_movie)<-[:mlgenome_acted_in]-(a:mlgenome_actor)
WHERE m.avgRating IS NOT NULL
WITH d.name AS Director, a.name AS Actor, count(m) AS Collabs, avg(m.avgRating) AS AvgRating
WHERE Collabs >= 3
RETURN Director, Actor, Collabs, round(AvgRating, 2) AS AvgRating
ORDER BY AvgRating DESC
LIMIT 10

Q35. Tag-Movie Associations with High Confidence (Which movie-tag pairs were most confidently agreed upon by users?)

Subcategory: T4.2 Grouped Node Degree Statistics by Label; T4.7 Union and Intersection

This query finds movie-tag pairs that are strongly agreed upon by users, based on the frequency of high survey scores (score >= 4).

cypher

MATCH (s:mlgenome_survey)-[:mlgenome_about_movie]->(m:mlgenome_movie),
      (s)-[:mlgenome_about_tag]->(t:mlgenome_tag)
WHERE s.score >= 4
RETURN m.title AS Movie, t.tag AS Tag, count(*) AS HighScoreCount
ORDER BY HighScoreCount DESC;

Equivalent query in GQL standard:

gql

MATCH (s:mlgenome_survey)-[:mlgenome_about_movie]->(m:mlgenome_movie), (s)-[:mlgenome_about_tag]->(t:mlgenome_tag)
WHERE s.score >= 4
RETURN m.title AS Movie, t.tag AS Tag, count(*) AS HighScoreCount
ORDER BY HighScoreCount DESC
LIMIT 10

Q36. Actors Frequently Appearing in High-Rated Movies with Different Directors (Highlights high-performing, collaborative actors trusted by many top directors.)

Subcategory: Other

This query finds actors who frequently appear in highly-rated movies (rating >= 4.0) with different directors, showcasing versatile and collaborative actors.

cypher

MATCH (a:mlgenome_actor)-[:mlgenome_acted_in]->(m:mlgenome_movie)<-[:mlgenome_directed]-(d:mlgenome_director)
WHERE m.avgRating >= 4.0
WITH a.name AS Actor,
     count(DISTINCT d.name) AS UniqueTopDirectors,
     count(DISTINCT m) AS TopRatedMovies
WHERE TopRatedMovies >= 3
RETURN Actor, UniqueTopDirectors, TopRatedMovies
ORDER BY UniqueTopDirectors DESC, TopRatedMovies DESC;

Equivalent query in GQL standard:

gql

MATCH (a:mlgenome_actor)-[:mlgenome_acted_in]->(m:mlgenome_movie)<-[:mlgenome_directed]-(d:mlgenome_director)
WHERE m.avgRating >= 4.0
WITH a.name AS Actor, count(DISTINCT d.name) AS UniqueTopDirectors, count(DISTINCT m) AS TopRatedMovies
WHERE TopRatedMovies >= 3
RETURN Actor, UniqueTopDirectors, TopRatedMovies
ORDER BY UniqueTopDirectors DESC, TopRatedMovies DESC
LIMIT 10

Q37. Most Frequently Used Words in Reviews

Subcategory: Other

This query tokenizes text reviews to extract the most frequently used words, filtering out common stop words.

cypher

// APOC is unavailable in PuppyGraph. Normalize common punctuation with native
// replace(), then tokenize with the openCypher split() function.
MATCH (r:mlgenome_review)
WHERE r.text IS NOT NULL
WITH replace(
       replace(
         replace(
           replace(
             replace(
               replace(
                 replace(
                   replace(
                     replace(
                       replace(toLower(r.text), '.', ' '),
                       ',', ' '),
                     '!', ' '),
                   '?', ' '),
                 ';', ' '),
               ':', ' '),
             '"', ' '),
           '-', ' '),
         '(', ' '),
       ')', ' ') AS normalizedText
UNWIND split(normalizedText, ' ') AS word
WITH word
WHERE size(word) > 3
  AND NOT (word IN [
    'this', 'that', 'with', 'from', 'have', 'your',
    'just', 'what', 'when', 'they', 'were', 'then',
    'there', 'would', 'which', 'could', 'really'
  ])
RETURN word, count(*) AS freq
ORDER BY freq DESC
LIMIT 20;

Note: Cannot be expressed in standard GQL because standard GQL defines no string tokenization function (split) and no list-unnesting clause (UNWIND) with which to expand the words of a review into rows.

Q38. Movies Most Frequently Called "Boring", "Disgusting", etc.

Subcategory: Other

This query counts reviews containing negative keywords (e.g., 'boring', 'disgusting', 'waste') for each movie to identify those with the most negative feedback.

cypher

MATCH (m:mlgenome_movie)-[:mlgenome_has_review]->(r:mlgenome_review)
WITH m, toLower(r.text) AS ReviewText
WHERE ReviewText CONTAINS 'boring'
   OR ReviewText CONTAINS 'disgusting'
   OR ReviewText CONTAINS 'waste'
   OR ReviewText CONTAINS 'terrible'
   OR ReviewText CONTAINS 'bad'
WITH m.title AS Movie, count(*) AS NegativeMentions
RETURN Movie, NegativeMentions
ORDER BY NegativeMentions DESC;

Equivalent query in GQL standard:

gql

MATCH (m:mlgenome_movie)-[:mlgenome_has_review]->(r:mlgenome_review)
WHERE toLower(r.text) CONTAINS 'boring'
OR toLower(r.text) CONTAINS 'disgusting'
OR toLower(r.text) CONTAINS 'waste'
OR toLower(r.text) CONTAINS 'terrible'
OR toLower(r.text) CONTAINS 'bad'
WITH m.title AS Movie, count(*) AS NegativeMentions
RETURN Movie, NegativeMentions
ORDER BY NegativeMentions DESC
LIMIT 10

Q39. Users With Highest Survey Agreement Accuracy (Measures users whose survey scores closely align with consensus (like those who gave 5 when others did too))

Subcategory: Other

This query ranks users by how closely their survey ratings align with the consensus ratings for each tag, highlighting the most consistent/accurate survey responders.

cypher

MATCH (u:mlgenome_user)-[:mlgenome_answered]->(s:mlgenome_survey)-[:mlgenome_about_tag]->(t:mlgenome_tag)
WITH u.user_id AS User, t.tag AS Tag, s.score AS UserScore
MATCH (s2:mlgenome_survey)-[:mlgenome_about_tag]->(t)
WHERE s2.score IS NOT NULL
WITH User, Tag, UserScore, avg(s2.score) AS Consensus
WITH User, avg(abs(UserScore - Consensus)) AS AvgDeviation, count(*) AS Count
WHERE Count >= 10
RETURN User,
       round(AvgDeviation * 100.0) / 100.0 AS AvgTagDisagreement,
       Count
ORDER BY AvgTagDisagreement ASC;

Equivalent query in GQL standard:

gql

MATCH (u:mlgenome_user)-[:mlgenome_answered]->(s:mlgenome_survey)-[:mlgenome_about_tag]->(t:mlgenome_tag)
WITH u.user_id AS User, t.tag AS Tag, s.score AS UserScore
MATCH (s2:mlgenome_survey)-[:mlgenome_about_tag]->(t)
WHERE s2.score IS NOT NULL
WITH User, Tag, UserScore, avg(s2.score) AS Consensus
WITH User, avg(abs(UserScore - Consensus)) AS AvgDeviation, count(*) AS Count
WHERE Count >= 10
RETURN User, round(AvgDeviation, 2) AS AvgTagDisagreement, Count
ORDER BY AvgTagDisagreement ASC
LIMIT 10