← Back to Stack Overflow Dataset

GraphFrames Implementation

Deployment and Query logic for GraphFrames on a Spark cluster with 7 worker nodes.

Schema Diagram and Graph Statistics

Stackof Graphframes Schema

Textual Graph Schema: 

Comment --[Commented_by]--> User
Comment --[On_answer]--> Post_answer
Comment --[On_question]--> Post_question
Post_answer --[Answers_by]--> User
Post_answer --[Answers_to]--> Post_question
Post_answer --[Last_edited_by]--> User
Post_question --[Accepts]--> Post_answer
Post_question --[Last_edited_by]--> User
Post_question --[Link_to]--> Post_question
Post_question --[Owned_by]--> User
Post_tag_wiki --[Created_by]--> User
Post_tag_wiki --[Last_edited_by]--> User
Post_tag_wiki_excerpt --[Created_by]--> User
Post_tag_wiki_excerpt --[Last_edited_by]--> User
Tag --[Has_wiki]--> Post_tag_wiki
Tag --[Has_wiki_excerpt]--> Post_tag_wiki_excerpt
Tag --[Tag_to]--> Post_question
User --[Earns]--> Badge
Vote --[Vote_on]--> Post_question
Vote --[Vote_on]--> Post_answer
Vote --[Vote_on]--> Comment

Node count by label: 

+---------------------+---------+
|type                 |count    |
+---------------------+---------+
|Post_tag_wiki        |55113    |
|Comment              |86754111 |
|Vote                 |236452885|
|Post_tag_wiki_excerpt|55115    |
|User                 |18712212 |
|Post_answer          |34024119 |
|Badge                |46135386 |
|Tag                  |63653    |
|Post_question        |23020127 |
+---------------------+---------+

Edge count by relationship: 

+----------------+---------+
|etype           |count    |
+----------------+---------+
|Vote_on         |365545435|
|Commented_by    |85362434 |
|Tag_to          |68338336 |
|Earns           |46135386 |
|On_question     |45700093 |
|On_answer       |41046017 |
|Answers_to      |34024119 |
|Answers_by      |33661703 |
|Last_edited_by  |22881481 |
|Owned_by        |22562265 |
|Accepts         |11755280 |
|Link_to         |5508987  |
|Created_by      |109836   |
|Has_wiki_excerpt|44966    |
|Has_wiki        |44966    |
+----------------+---------+

Data Loading

Overview

Data import is handled in four parts. First we upload all the raw data files into DataFrames. Then we define nodes and edges. Finally, we create graph schema.

Import Data

The first thing we need to do is to load all CSV files into DataFrames.

python 
%%time

# Load Stack Overflow relational data from HDFS
badge_df = spark.read.option("header", True).csv("hdfs:///stackoverflow/badges.csv")
comment_df = spark.read.option("header", True).csv("hdfs:///stackoverflow/comments_final.csv")
post_answer_df = spark.read.option("header", True).csv("hdfs:///stackoverflow/posts_answers_fixed.csv")
post_question_df = spark.read.option("header", True).csv("hdfs:///stackoverflow/posts_questions_cleaned1.csv")
post_tag_wiki_df = spark.read.option("header", True).csv("hdfs:///stackoverflow/posts_tag_wiki_clean.csv")
post_tag_wiki_excerpt_df = spark.read.option("header", True).csv("hdfs:///stackoverflow/posts_tag_wiki_excerpt_100PCT.csv")
user_df = spark.read.option("header", True).csv("hdfs:///stackoverflow/users_clean.csv")
tag_df = spark.read.option("header", True).csv("hdfs:///stackoverflow/tags.csv")
vote_df = spark.read.option("header", True).csv("hdfs:///stackoverflow/votes.csv")
post_links_df = spark.read.option("header", True).csv("hdfs:///stackoverflow/posts_links.csv")

Define Node

First we define the Users node.

python 
user_vertices = user_df.selectExpr(
    "concat('user_', cast(id as string)) as id",
    "'User' as type",
    "display_name",
    "about_me",
    "age",
    "creation_date",
    "last_access_date",
    "location",
    "cast(reputation as long) as reputation",
    "cast(up_votes as long) as up_votes",
    "cast(down_votes as long) as down_votes",
    "cast(views as long) as views",
    "profile_image_url",
    "website_url"
).dropDuplicates(["id"])

Then we define the Badges node.

python 
badge_vertices = badge_df.selectExpr(
    "concat('badge_', cast(id as string)) as id",
    "'Badge' as type",
    "name",
    "date",
    "cast(user_id as long) as user_id",
    "class",
"tag_based"
).dropDuplicates(["id"])

Then we define the Comments node.

python 
comment_vertices = comment_df.selectExpr(
    "concat('comment_', cast(id as string)) as id",
    "'Comment' as type",
    "text",
    "creation_date",
    "cast(post_id as long) as post_id",
    "cast(user_id as long) as user_id",
    "user_display_name",
    "cast(score as long) as score"
).dropDuplicates(["id"])

Then we define the Post_Answers node.

python 
post_answer_vertices = post_answer_df.selectExpr(
    "concat('answer_', cast(id as string)) as id",
    "'Post_answer' as type",
    "title",
    "body",
    "cast(accepted_answer_id as long) as accepted_answer_id",
    "cast(answer_count as long) as answer_count",
    "cast(comment_count as long) as comment_count",
    "community_owned_date",
    "creation_date",
    "cast(favorite_count as long) as favorite_count",
    "last_activity_date",
    "last_edit_date",
    "last_editor_display_name",
    "cast(last_editor_user_id as long) as last_editor_user_id",
    "owner_display_name",
    "cast(owner_user_id as long) as owner_user_id",
    "cast(parent_id as long) as parent_id",
    "cast(post_type_id as long) as post_type_id",
    "cast(score as long) as score",
    "tags",
    "cast(view_count as long) as view_count"
).dropDuplicates(["id"])

Then we define the Post_Questions node.

python 
post_question_vertices = post_question_df.selectExpr(
    "concat('question_', cast(id as string)) as id",
    "'Post_question' as type",
    "cast(accepted_answer_id as long) as accepted_answer_id",
    "cast(answer_count as long) as answer_count",
    "cast(comment_count as long) as comment_count",
    "community_owned_date",
    "creation_date",
    "cast(favorite_count as long) as favorite_count",
    "last_activity_date",
    "last_edit_date",
    "last_editor_display_name",
    "cast(last_editor_user_id as long) as last_editor_user_id",
    "owner_display_name",
    "cast(owner_user_id as long) as owner_user_id",
    "cast(parent_id as long) as parent_id",
    "cast(post_type_id as long) as post_type_id",
    "cast(score as long) as score",
    "tags",
    "cast(view_count as long) as view_count"
).dropDuplicates(["id"])

Then we define the Tags node.

python 
tag_vertices = tag_df.selectExpr(
    "concat('tag_', cast(id as string)) as id",
    "'Tag' as type",
    "tag_name",
    "cast(count as long) as count",
    "cast(excerpt_post_id as long) as excerpt_post_id",
    "cast(wiki_post_id as long) as wiki_post_id"
).dropDuplicates(["id"])

Then we define the Votes node.

python 
vote_vertices = vote_df.selectExpr(
    "concat('vote_', cast(id as string)) as id",
    "'Vote' as type",
    "creation_date",
    "cast(post_id as long) as post_id",
    "cast(vote_type_id as long) as vote_type_id"
).dropDuplicates(["id"])

Then we define the Post_Tag_Wiki node.

python 
post_tag_wiki_vertices = post_tag_wiki_df.selectExpr(
    "concat('wiki_', cast(id as string)) as id",
    "'Post_tag_wiki' as type",
    "title",
    "body",
    "cast(comment_count as long) as comment_count",
    "creation_date",
    "last_activity_date",
    "last_edit_date",
    "last_editor_display_name",
    "cast(last_editor_user_id as long) as last_editor_user_id",
    "owner_display_name",
    "cast(owner_user_id as long) as owner_user_id",
    "cast(post_type_id as long) as post_type_id",
    "cast(score as long) as score"
).dropDuplicates(["id"])

Then we define the Post_Tag_Wiki_Excerpt node.

python 
post_tag_wiki_excerpt_vertices = post_tag_wiki_excerpt_df.selectExpr(
    "concat('wiki_excerpt_', cast(id as string)) as id",
    "'Post_tag_wiki_excerpt' as type",
    "title",
    "body",
    "cast(comment_count as long) as comment_count",
    "creation_date",
    "last_activity_date",
    "last_edit_date",
    "last_editor_display_name",
    "cast(last_editor_user_id as long) as last_editor_user_id",
    "owner_display_name",
    "cast(owner_user_id as long) as owner_user_id",
    "cast(post_type_id as long) as post_type_id",
    "cast(score as long) as score"
).dropDuplicates(["id"])

Define Edge

Next step is to define edges. First we define the OWNED_BY edges from Post_question to User.

python 
from pyspark.sql.functions import col, lit, explode, split, regexp_replace, trim, lower, concat
# Create Edge - User created Question
e_question_created_by_user = post_question_df.selectExpr(
    "concat('question_', cast(id as string)) as src",
    "concat('user_', cast(owner_user_id as string)) as dst",
    "'Owned_by' as etype"
).filter("owner_user_id IS NOT NULL").dropDuplicates()

Then we define the LAST_EDITED_BY edges from Post_question to User.

python 
# Create Edge - User last edited Question
e_question_last_edited_by_user = post_question_df.selectExpr(
    "concat('question_', cast(id as string)) as src",
    "concat('user_', cast(last_editor_user_id as string)) as dst",
    "'Last_edited_by' as etype"
).filter("last_editor_user_id IS NOT NULL").dropDuplicates()

Then we define the ANSWERED_BY edges from Post_answer to User.

python 
# Create Edge - User created Answer
e_answer_created_by_user = post_answer_df.selectExpr(
    "concat('answer_', cast(id as string)) as src",
    "concat('user_', cast(owner_user_id as string)) as dst",
    "'Answers_by' as etype"
).filter("owner_user_id IS NOT NULL").dropDuplicates()

Then we define the LAST_EDITED_BY edges from Post_answer to User.

python 
# Create Edge - User last edited Answer
e_answer_last_edited_by_user = post_answer_df.selectExpr(
    "concat('answer_', cast(id as string)) as src",
    "concat('user_', cast(last_editor_user_id as string)) as dst",
    "'Last_edited_by' as etype"
).filter("last_editor_user_id IS NOT NULL").dropDuplicates()

Then we define the CREATED_BY edges from Post_tag_wiki to User.

python 
# Create Edge - User created Tag Wiki
e_wiki_created_by_user = post_tag_wiki_df.selectExpr(
    "concat('wiki_', cast(id as string)) as src",
    "concat('user_', cast(owner_user_id as string)) as dst",
    "'Created_by' as etype"
).filter("owner_user_id IS NOT NULL").dropDuplicates()

Then we define the LAST_EDITED_BY edges from Post_tag_wiki to User.

python 
# Create Edge - User last edited Tag Wiki
e_wiki_last_edited_by_user = post_tag_wiki_df.selectExpr(
    "concat('wiki_', cast(id as string)) as src",
    "concat('user_', cast(last_editor_user_id as string)) as dst",
    "'Last_edited_by' as etype"
).filter("last_editor_user_id IS NOT NULL").dropDuplicates()

Then we define the CREATED_BY edges from Post_tag_wiki_excerpt to User.

python 
# Create Edge - User created Tag Wiki Excerpt
e_wiki_excerpt_created_by_user = post_tag_wiki_excerpt_df.selectExpr(
    "concat('wiki_excerpt_', cast(id as string)) as src",
    "concat('user_', cast(owner_user_id as string)) as dst",
    "'Created_by' as etype"
).filter("owner_user_id IS NOT NULL").dropDuplicates()

Then we define the LAST_EDITED_BY edges from Post_tag_wiki_excerpt to User.

python 
# Create Edge - User last edited Tag Wiki Excerpt
e_wiki_excerpt_last_edited_by_user = post_tag_wiki_excerpt_df.selectExpr(
    "concat('wiki_excerpt_', cast(id as string)) as src",
    "concat('user_', cast(last_editor_user_id as string)) as dst",
    "'Last_edited_by' as etype"
).filter("last_editor_user_id IS NOT NULL").dropDuplicates()

Then we define the EARNS edges from User to Badge.

python 
# Create Edge - User earns Badge
e_user_earns_badge = badge_df.selectExpr(
    "concat('user_', cast(user_id as string)) as src",
    "concat('badge_', cast(id as string)) as dst",
    "'Earns' as etype"
).filter("user_id IS NOT NULL").dropDuplicates()

Then we define the ANSWERS_TO edges from Post_answer to Post_question.

python 
# Create Edge - Answer to Question (linking child to parent)
e_answer_to_question = post_answer_df.selectExpr(
    "concat('answer_', cast(id as string)) as src",
    "concat('question_', cast(parent_id as string)) as dst",
    "'Answers_to' as etype"
).filter("parent_id IS NOT NULL").dropDuplicates()

Then we define the ACCEPTS edges from Post_question to Post_answer.

python 
# Create Edge - Question accepts Answer (the "correct" solution link)
e_question_accepts_answer = post_question_df.filter(
    "accepted_answer_id IS NOT NULL"
).selectExpr(
    "concat('question_', cast(id as string)) as src",
    "concat('answer_', cast(accepted_answer_id as string)) as dst",
    "'Accepts' as etype"
).dropDuplicates()

Then we define the COMMENTED_BY edges from Comment to User.

python 
# Create Edge - User commented
e_comment_by_user = comment_df.selectExpr(
    "concat('comment_', cast(id as string)) as src",
    "concat('user_', cast(user_id as string)) as dst"
).filter("user_id IS NOT NULL AND user_id != '' AND user_id != '0'")
 .dropDuplicates(['src', 'dst'])
 .withColumn("etype", lit("Commented_by"))

Then we define the ON_QUESTION edges from Comment to Post_question.

python 
# Create Edge - Link each Comment to its parent Question
c = comment_df.alias("c")
q = post_question_df.alias("q")

e_comment_on_question = c.join(
    q,
    col("c.post_id") == col("q.id"),
    "inner"
).select(
    concat(lit("comment_"), col("c.id").cast("string")).alias("src"),
    concat(lit("question_"), col("q.id").cast("string")).alias("dst"),
    lit("On_question").alias("etype")
).dropDuplicates()

Then we define the ON_ANSWER edges from Comment to Post_answer.

python 
# Create Edge - Link each Comment to its parent Answer
c = comment_df.alias("c")
a = post_answer_df.alias("a")

e_comment_on_answer = c.join(
    a,
    col("c.post_id") == col("a.id"),
    "inner"
).select(
    concat(lit("comment_"), col("c.id").cast("string")).alias("src"),
    concat(lit("answer_"), col("a.id").cast("string")).alias("dst"),
    lit("On_answer").alias("etype")
).dropDuplicates()

Then we define the VOTE_ON edges from Vote to Post_question.

python 
# Create Edge - Link Votes to parent Questions
e_vote_on_question = vote_df.alias("v")
    .join(post_question_df.alias("q"), col("v.post_id") == col("q.id"), "inner")
    .select(
        concat(lit("vote_"), col("v.id").cast("string")).alias("src"),
        concat(lit("question_"), col("q.id").cast("string")).alias("dst"),
        lit("Vote_on").alias("etype")
    ).dropDuplicates()

Then we define the VOTE_ON edges from Vote to Post_answer.

python 
# Create Edge - Link Votes to parent Answers
e_vote_on_answer = vote_df.alias("v")
    .join(post_answer_df.alias("a"), col("v.post_id") == col("a.id"), "inner")
    .select(
        concat(lit("vote_"), col("v.id").cast("string")).alias("src"),
        concat(lit("answer_"), col("a.id").cast("string")).alias("dst"),
        lit("Vote_on").alias("etype")
    ).dropDuplicates()

Then we define the VOTE_ON edges from Vote to Comment.

python 
# Create Edge - Link Votes to parent Comments
e_vote_on_comment = vote_df.alias("v")
    .join(comment_df.alias("c"), col("v.post_id") == col("c.id"), "inner")
    .select(
        concat(lit("vote_"), col("v.id").cast("string")).alias("src"),
        concat(lit("comment_"), col("c.id").cast("string")).alias("dst"),
        lit("Vote_on").alias("etype")
    ).dropDuplicates()

Then we define the TAG_TO edges from Tag to Post_question.

python 
# 1. Extract and unroll tags from the bracketed XML format
filter(col("tags").isNotNull() & (col("tags") != ""))
    .withColumn("tags_cleaned", regexp_replace(col("tags"), "><", "|"))
    .withColumn("tags_cleaned", regexp_replace(col("tags_cleaned"), "[<>]", ""))
    .withColumn("tag", explode(split(col("tags_cleaned"), "\|")))
    .withColumn("tag", trim(lower(col("tag"))))
    .filter(col("tag") != "")
    .select(
        concat(lit("question_"), col("id").cast("string")).alias("dst"),
        col("tag").alias("tag_name")
    )

# 2. Map tag names to their unique vertex IDs
tag_map = tag_df.select(
    concat(lit("tag_"), col("id").cast("string")).alias("tag_id"),
    lower(col("tag_name")).alias("tag_name")
).distinct()

# 3. Join and create the final directed edges
e_tag_to_question = question_tags.join(tag_map, on="tag_name", how="inner")
    .select(
        col("tag_id").alias("src"),
        col("dst"),
        lit("Tag_to").alias("etype")
    ).dropDuplicates()

Then we define the HAS_WIKI edges from Tag to its full Wiki post.

python 
# Create Edge - Link Tag to its full Wiki post
e_tag_has_wiki = tag_df.filter(
    "wiki_post_id IS NOT NULL"
).selectExpr(
    "concat('tag_', cast(id as string)) as src",
    "concat('wiki_', cast(wiki_post_id as string)) as dst",
    "'Has_wiki' as etype"
).dropDuplicates()

Then we define the HAS_WIKI_EXCERPT edges from Tag to its short Post_tag_wiki_excerpt post.

python 
# Create Edge - Link Tag to its short Excerpt post
e_tag_has_wiki_excerpt = tag_df.filter(
    "excerpt_post_id IS NOT NULL"
).selectExpr(
    "concat('tag_', cast(id as string)) as src",
    "concat('wiki_excerpt_', cast(excerpt_post_id as string)) as dst",
    "'Has_wiki_excerpt' as etype"
).dropDuplicates()

Then we define the TAG_TO edges from Tag to Post_question.

python 
# Identify all existing questions to validate links
valid_question_ids = post_question_df.select(
    col("id").cast("string").alias("numeric_id")
).distinct()

# Filter and join to create validated inter-question links
e_question_link_to_question = post_links_df
    .filter(
        "post_id IS NOT NULL AND related_post_id IS NOT NULL "
        "AND post_id != '' AND related_post_id != '' "
        "AND post_id != '0' AND related_post_id != '0'"
    )
    .join(valid_question_ids.select(col("numeric_id").alias("src_check")),
          post_links_df.post_id == col("src_check"), "inner")
    .join(valid_question_ids.select(col("numeric_id").alias("dst_check")),
          post_links_df.related_post_id == col("dst_check"), "inner")
    .selectExpr(
        "concat('question_', cast(post_id as string)) as src",
        "concat('question_', cast(related_post_id as string)) as dst",
        "'Link_to' as etype"
    ).dropDuplicates()

Define Graph

Now that we have defined our vertex and edge, we can create graph schema with those. First we need to union all nodes and edges into one vertices and one edges variable.

python 
from functools import reduce
from pyspark.sql import DataFrame

# List of all specialized vertex DataFrames
vertex_frames = [
    user_vertices,
    badge_vertices,
    comment_vertices,
    post_question_vertices,
    post_answer_vertices,
    tag_vertices,
    vote_vertices,
    post_tag_wiki_vertices,
    post_tag_wiki_excerpt_vertices
]

# Perform a wide union to create the master vertex set
vertices = reduce(
    lambda a, b: a.unionByName(b, allowMissingColumns=True),
    vertex_frames
).dropDuplicates(["id"]).filter("id IS NOT NULL")
python 
# Union all edges into a single master DataFrame for GraphFrames initialization
edges = e_question_created_by_user
    .unionByName(e_question_last_edited_by_user,     allowMissingColumns=True)
    .unionByName(e_answer_created_by_user,            allowMissingColumns=True)
    .unionByName(e_answer_last_edited_by_user,        allowMissingColumns=True)
    .unionByName(e_wiki_created_by_user,              allowMissingColumns=True)
    .unionByName(e_wiki_last_edited_by_user,          allowMissingColumns=True)
    .unionByName(e_wiki_excerpt_created_by_user,      allowMissingColumns=True)
    .unionByName(e_wiki_excerpt_last_edited_by_user,  allowMissingColumns=True)
    .unionByName(e_user_earns_badge,                  allowMissingColumns=True)
    .unionByName(e_answer_to_question,                allowMissingColumns=True)
    .unionByName(e_question_accepts_answer,           allowMissingColumns=True)
    .unionByName(e_comment_by_user,                   allowMissingColumns=True)
    .unionByName(e_comment_on_question,               allowMissingColumns=True)
    .unionByName(e_comment_on_answer,                 allowMissingColumns=True)
    .unionByName(e_vote_on_question,                  allowMissingColumns=True)
    .unionByName(e_vote_on_answer,                    allowMissingColumns=True)
    .unionByName(e_vote_on_comment,                   allowMissingColumns=True)
    .unionByName(e_tag_to_question,                   allowMissingColumns=True)
    .unionByName(e_tag_has_wiki,                      allowMissingColumns=True)
    .unionByName(e_tag_has_wiki_excerpt,              allowMissingColumns=True)
    .unionByName(e_question_link_to_question,         allowMissingColumns=True)
python 
from graphframes import GraphFrames
g = GraphFrames(vertices, edges)

Queries

Select a query below to view its logic and description.

1. Neighborhood
No queries in this category.
2. Path
3. Pattern
4. Aggregation
5. Analytics
6. Others

Q1. User Pairs with Most Question-Answer Interactions

Subcategory: T3.5 Two-hop Join Pattern

This query finds answerer-questioner pairs connected through answer posts and question posts, counts how many times one user answered another user's questions, and returns pairs with at least five such interactions.

python 
%%time

# Define the structural pattern: Asker -> Question <- Answer <- Answerer
motif_q1 = g.find("(q)-[e1]->(asker); (a)-[e2]->(q); (a)-[e3]->(answerer)")
    .filter("e1.etype = 'Owned_by'")
    .filter("e2.etype = 'Answers_to'")
    .filter("e3.etype = 'Answers_by'")
    .filter("q.type = 'Post_question'")
    .filter("a.type = 'Post_answer'")
    .filter("asker.type = 'User'")
    .filter("answerer.type = 'User'")
    .filter("asker.id != answerer.id")
    .groupBy(
        regexp_replace(col("asker.id"),    "user_", "").alias("asker_user_id"),
        regexp_replace(col("answerer.id"), "user_", "").alias("answerer_user_id")
    )
    .agg(count("*").alias("interaction_count"))
    .filter("interaction_count >= 5")
    .orderBy(desc("interaction_count"))
motif_q1.show(20, truncate=False)

Equivalent query in GQL standard:

gql 
MATCH (a:Post_answer)-[:Answers_to]->(q:Post_question)
MATCH (a)-[:Answers_by]->(answerer:User)
MATCH (q)-[:Owned_by]->(questioner:User)
WHERE answerer.id <> questioner.id
WITH (answerer.id + ':' + questioner.id) AS pair_key,
    count(*) AS interaction_count
WHERE interaction_count >= 5
RETURN pair_key, interaction_count
ORDER BY interaction_count DESC
LIMIT 100;

Q2. Tags with Highest Question-to-Answer Ratio

Subcategory: T4.2 Grouped Node Degree Statistics by Label

This query groups questions by tag, counts both tagged questions and answers attached to those questions, then ranks tags by the highest question-to-answer ratio.

python 
# Step 1: tag -> question (Tag_to)
tag_question_motif = g.find("(t)-[e1]->(q)")
    .filter("e1.etype = 'Tag_to'")
    .filter("t.type = 'Tag'")
    .filter("q.type = 'Post_question'")
    .select(
        col("t.id").alias("tag_id"),
        col("t.tag_name"),
        col("q.id").alias("question_id")
    )

# Step 2: answer -> question (Answers_to)
answer_question_motif = g.find("(a)-[e2]->(q)")
    .filter("e2.etype = 'Answers_to'")
    .filter("a.type = 'Post_answer'")
    .filter("q.type = 'Post_question'")
    .select(
        col("q.id").alias("question_id"),
        col("a.id").alias("answer_id")
    )

tag_stats = tag_question_motif.alias("tq")
    .join(answer_question_motif.alias("aq"),
        col("tq.question_id") == col("aq.question_id"), "left")
    .groupBy("tq.tag_id", "tq.tag_name")
    .agg(
        countDistinct("tq.question_id").alias("question_count"),
        countDistinct("aq.answer_id").alias("answer_count")
    )
    .filter("answer_count > 0")
    .withColumn("ratio", col("question_count") / col("answer_count"))
    .orderBy(desc("ratio"))

tag_stats.show(20, truncate=False)

Equivalent query in GQL standard:

gql 
MATCH (t:Tag)-[:Tag_to]->(q:Post_question)
WITH t, count(q) AS question_count
MATCH (t)-[:Tag_to]->(q2:Post_question)<-[:Answers_to]-(a:Post_answer)
WITH t.tag_name AS tag_name,
    question_count,
    count(a) AS answer_count
WHERE answer_count > 0
RETURN tag_name,
    question_count,
    answer_count,
    toFloat(question_count) / toFloat(answer_count) AS ratio
ORDER BY ratio DESC
LIMIT 200;

Q3. Questions with Most Views But No Accepted Answer

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

This query finds highly viewed questions that do not have an accepted answer recorded, returning each question's ID, view count, and answer count ordered by views.

python 
%%time

# Query to find high-traffic questions with no accepted solution
result_q3 = g.vertices
    .filter("type = 'Post_question'")
    .filter("accepted_answer_id = 0 OR accepted_answer_id IS NULL")
    .filter("view_count IS NOT NULL")
    .select(
        regexp_replace(col("id"), "question_", "").alias("question_id"),
        col("view_count"),
        col("answer_count")
    )
    .orderBy(desc("view_count"))
    .limit(200)

result_q3.show(20, truncate=False)

Equivalent query in GQL standard:

gql 
MATCH (q:Post_question)
WHERE (q.accepted_answer_id IS NULL
    OR q.accepted_answer_id = ''
    OR q.accepted_answer_id = '0'
    OR q.accepted_answer_id = 'None')
RETURN q.id AS question_id,
    toInteger(q.view_count) AS view_count,
    toInteger(q.answer_count) AS answer_count
ORDER BY toInteger(q.view_count) DESC
LIMIT 200;

Q4. Users Who Earn Badges Fastest After Account Creation

Subcategory: T4.4 Grouped Edge Count and Property Statistics

This query groups badge-earning events by user, estimates how many days elapsed between each user's account creation and first badge date, and returns the fastest badge earners.

python 
%%time

# Query to find the quickest path from account creation to first badge
motif_q4 = g.find("(u)-[e]->(b)")
    .filter("e.etype = 'Earns'")
    .filter("u.type = 'User'")
    .filter("b.type = 'Badge'")
    .select(
        col("u.id").alias("user_id"),
        col("u.display_name"),
        col("u.creation_date").alias("user_creation_date"),
        col("b.date").alias("badge_date")
    )
    .withColumn("days_to_badge", datediff(col("badge_date"), col("user_creation_date")))
    .groupBy("user_id", "display_name")
    .agg(sql_min("days_to_badge").alias("min_time_to_first_badge"))
    .filter("min_time_to_first_badge >= 0")
    .select(
        regexp_replace(col("user_id"), "user_", "").alias("id"),
        col("display_name"),
        col("min_time_to_first_badge")
    )
    .orderBy(asc("min_time_to_first_badge"), asc("id"))

motif_q4.show(20, truncate=False)

Equivalent query in GQL standard:

gql 
MATCH (u:User)-[:Earns]->(b:Badge)
WHERE u.creation_date IS NOT NULL
    AND b.date IS NOT NULL
    AND u.creation_date <> ''
    AND b.date <> ''
WITH u.id AS user_id,
     u.display_name AS display_name,
     u.creation_date AS creation_date,
     min(
        (toInteger(substring(b.date, 0, 4)) - toInteger(substring(u.creation_date, 0, 4))) * 365 +
        (toInteger(substring(b.date, 5, 2)) - toInteger(substring(u.creation_date, 5, 2))) * 30 +
        (toInteger(substring(b.date, 8, 2)) - toInteger(substring(u.creation_date, 8, 2)))
     ) AS min_days
WHERE min_days >= 0
RETURN user_id, display_name, min_days
ORDER BY min_days ASC, user_id ASC
LIMIT 20;

Q5. Percentage of Questions Answered Over the Years

Subcategory: Other

This query groups questions by creation year, counts all questions and questions with at least one answer, then returns the yearly answer rate as a percentage.

python 
%%time

# Analyze the percentage of questions that receive answers year-over-year
result_q5 = g.vertices
    .filter("type = 'Post_question'")
    .filter("creation_date IS NOT NULL")
    .withColumn("year", year(col("creation_date")))
    .groupBy("year")
    .agg(
        count("id").alias("total_questions"),
        sql_sum(when(col("answer_count") > 0, 1).otherwise(0)).alias("answered_questions")
    )
    .withColumn("answer_rate", col("answered_questions") / col("total_questions") * 100)
    .orderBy(asc("year"))

result_q5.show(50, truncate=False)

Equivalent query in GQL standard:

gql 
MATCH (q:Post_question)
WHERE q.creation_date IS NOT NULL
    AND q.creation_date <> ''
WITH substring(q.creation_date, 0, 4) AS year_val,
     count(q) AS total_questions,
     sum(CASE WHEN toInteger(q.answer_count) > 0 THEN 1 ELSE 0 END) AS answered_questions
RETURN year_val,
    total_questions,
    answered_questions,
    round(toFloat(answered_questions) * 100.0 / toFloat(total_questions), 5) AS answer_rate
ORDER BY year_val ASC;

Q6. Top 10 "Easiest" Gold Badges to Earn

Subcategory: T4.4 Grouped Edge Count and Property Statistics

This query focuses on gold badges, counts distinct earners for each badge, estimates the fastest time from account creation to earning that badge, and returns the most commonly earned gold badges.

python 
%%time

# Query to analyze the rarest and fastest-earned Gold Badges
motif_q6 = g.find("(u)-[e]->(b)")
    .filter("e.etype = 'Earns'")
    .filter("u.type = 'User'")
    .filter("b.type = 'Badge'")
    .filter("b.class = '1'")
    .select(
        col("b.name").alias("badge_name"),
        col("u.id").alias("user_id"),
        col("u.creation_date").alias("user_creation_date"),
        col("b.date").alias("badge_date")
    )
    .withColumn("days_to_earn", datediff(col("badge_date"), col("user_creation_date")))
    .groupBy("badge_name")
    .agg(
        countDistinct("user_id").alias("total_earners"),
        sql_min("days_to_earn").alias("min_time_to_earn")
    )
    .filter("min_time_to_earn >= 0")
    .orderBy(desc("total_earners"))
    .limit(10)

motif_q6.show(10, truncate=False)

Equivalent query in GQL standard:

gql 
MATCH (u:User)-[:Earns]->(b:Badge)
WHERE b.class = '1'
    AND u.creation_date IS NOT NULL
    AND b.date IS NOT NULL
    AND u.creation_date <> ''
    AND b.date <> ''
WITH b.name AS badge_name,
     count(DISTINCT u.id) AS total_earners,
     min(
        (toInteger(substring(b.date, 0, 4)) - toInteger(substring(u.creation_date, 0, 4))) * 365 +
        (toInteger(substring(b.date, 5, 2)) - toInteger(substring(u.creation_date, 5, 2))) * 30 +
        (toInteger(substring(b.date, 8, 2)) - toInteger(substring(u.creation_date, 8, 2)))
     ) AS min_days
WHERE min_days >= 0
RETURN badge_name, total_earners, min_days
ORDER BY total_earners DESC
LIMIT 10;

Q7. Vote Distribution

Subcategory: T5.1 Degree Centrality

This query ranks users by the total number of votes received across their questions and answers, combining both incoming vote relationships into one activity score.

python 
%%time

# Path A: Votes on questions -> trace to question owner
votes_q = g.find("(v)-[e1]->(q); (q)-[e2]->(u)")
    .filter("e1.etype = 'Vote_on'")
    .filter("e2.etype = 'Owned_by'")
    .filter("v.type = 'Vote'")
    .filter("q.type = 'Post_question'")
    .filter("u.type = 'User'")
    .select(col("u.id").alias("user_id"))

# Path B: Votes on answers -> trace to answer author
votes_a = g.find("(v)-[e1]->(a); (a)-[e2]->(u)")
    .filter("e1.etype = 'Vote_on'")
    .filter("e2.etype = 'Answers_by'")
    .filter("v.type = 'Vote'")
    .filter("a.type = 'Post_answer'")
    .filter("u.type = 'User'")
    .select(col("u.id").alias("user_id"))

# Consolidate and rank users by total vote volume
result_q7 = votes_q.union(votes_a)
    .groupBy("user_id")
    .agg(count("*").alias("total_votes"))
    .join(g.vertices.filter("type = 'User'").alias("u"),
    col("user_id") == col("u.id"), "inner")
    .select(
        regexp_replace(col("u.id"), "user_", "").alias("id"),
        col("u.display_name"),
        col("total_votes")
    )
    .orderBy(desc("total_votes"))

result_q7.show(20, truncate=False)

Equivalent query in GQL standard:

gql 
MATCH (u:User)
OPTIONAL MATCH (q:Post_question)-[:Owned_by]->(u)
OPTIONAL MATCH (vq:Vote)-[:Vote_on_question]->(q)
WITH u, count(vq) AS q_votes
OPTIONAL MATCH (a:Post_answer)-[:Answers_by]->(u)
OPTIONAL MATCH (va:Vote)-[:Vote_on_answer]->(a)
WITH u.id AS user_id,
     u.display_name AS display_name,
     q_votes + count(va) AS total_votes
WHERE total_votes > 0
RETURN user_id, display_name, total_votes
ORDER BY total_votes DESC
LIMIT 20;

Q8. Most Linked Questions

Subcategory: T5.1 Degree Centrality

This query counts incoming Link_to relationships for each question and returns the questions that are referenced most often by other questions.

python 
%%time

# Identify the most linked-to (canonical) questions in the graph
motif_q8 = g.find("(q1)-[e]->(q2)")
    .filter("e.etype = 'Link_to'")
    .filter("q1.type = 'Post_question'")
    .filter("q2.type = 'Post_question'")
    .groupBy(col("q2.id").alias("question_id"))
    .agg(count("*").alias("link_count"))
    .select(
        regexp_replace(col("question_id"), "question_", "").alias("id"),
        col("link_count")
    )
    .orderBy(desc("link_count"))

motif_q8.show(20, truncate=False)

Equivalent query in GQL standard:

gql 
MATCH (src:Post_question)-[:Link_to]->(target:Post_question)
WITH target.id AS question_id,
     count(src) AS link_count
WHERE link_count > 0
RETURN question_id, link_count
ORDER BY link_count DESC
LIMIT 20;

Q9. Users Who Comment Most

Subcategory: T5.1 Degree Centrality

This query counts comments authored by each user and returns the most active commenters by total outgoing comment activity.

python 
%%time

# Identify users with the highest volume of comments in the graph
motif_q9 = g.find("(c)-[e]->(u)")
    .filter("e.etype = 'Commented_by'")
    .filter("c.type = 'Comment'")
    .filter("u.type = 'User'")
    .groupBy(col("u.id").alias("user_id"), col("u.display_name"))
    .agg(count("*").alias("total_comments"))
    .select(
        regexp_replace(col("user_id"), "user_", "").alias("id"),
        col("display_name"),
        col("total_comments")
    )
    .orderBy(desc("total_comments"))

motif_q9.show(20, truncate=False)

Equivalent query in GQL standard:

gql 
MATCH (c:Comment)-[:Commented_by]->(u:User)
WITH u.id AS user_id,
     u.display_name AS display_name,
     count(c) AS total_comments
WHERE total_comments > 0
RETURN user_id, display_name, total_comments
ORDER BY total_comments DESC
LIMIT 20;

Q10. Users with Most Accepted Answers

Subcategory: T5.1 Degree Centrality

This query counts accepted-answer relationships per user and returns the users whose answers were selected as accepted solutions most often.

python 
%%time

# Identify users with the highest number of officially accepted solutions
motif_q10 = g.find("(q)-[e1]->(a); (a)-[e2]->(u)")
    .filter("e1.etype = 'Accepts'")
    .filter("e2.etype = 'Answers_by'")
    .filter("q.type = 'Post_question'")
    .filter("a.type = 'Post_answer'")
    .filter("u.type = 'User'")
    .groupBy(col("u.id").alias("user_id"), col("u.display_name"))
    .agg(count("*").alias("accepted_answer_count"))
    .select(
        regexp_replace(col("user_id"), "user_", "").alias("id"),
        col("display_name"),
        col("accepted_answer_count")
    )
    .orderBy(desc("accepted_answer_count"))
motif_q10.show(20, truncate=False)

Equivalent query in GQL standard:

gql 
MATCH (q:Post_question)-[:Accepts]->(a:Post_answer)-[:Answers_by]->(u:User)
WITH u.id AS user_id,
     u.display_name AS display_name,
     count(a) AS accepted_count
WHERE accepted_count > 0
RETURN user_id, display_name, accepted_count
ORDER BY accepted_count DESC
LIMIT 20;