PuppyGraph Stack Overflow Implementation

Schema Diagram and Graph Statistics

Total node count: 445,272,721

Total edge count: 782,719,894

Data Loading

The data ingestion pipeline has two stages. Initially, raw Stack Overflow datasets (in CSV format) are stored in Hadoop Distributed File System (HDFS). We then utilize Spark to perform schema enforcement and type casting, persisting the structured data as Delta Lake tables registered within a Hive Metastore. PuppyGraph then interfaces directly with this Metastore via a schema.json file, enabling a zero-ETL graph overlay on the existing data lake.

Metastore Import

First we create a Spark session using its Python API.

python

from pyspark.sql import SparkSession

spark = (
    SparkSession.builder
        .appName("stackoverflow")
        .master("spark://master:7077")
        # --- Resource Allocation ---
        .config("spark.executor.instances", "2")
        .config("spark.executor.cores", "2")
        .config("spark.executor.memory", "20g")
        .config("spark.driver.memory", "4g")
        # --- Delta Lake Integration ---
        .config("spark.jars.packages", "io.delta:delta-core_2.12:2.4.0")
        .config("spark.sql.extensions", "io.delta.sql.DeltaSparkSessionExtension")
        .config("spark.sql.catalog.spark_catalog", "org.apache.spark.sql.delta.catalog.DeltaCatalog")
        # --- HDFS File System ---
        .config("spark.hadoop.fs.defaultFS", "hdfs://master:9000")
        .config("spark.sql.warehouse.dir", "hdfs://master:9000/spark-warehouse")
        # --- Hive Metastore Connection ---
        .config("spark.sql.catalogImplementation", "hive")
        .config("hive.metastore.uris", "thrift://master:9083")
        .getOrCreate()
)

Next, we create the database and table, and load data from HDFS into the Hive Metastore.

python

# Create a logical database in the Hive Metastore
spark._jsparkSession.sql("CREATE DATABASE IF NOT EXISTS stackoverflow")

# Ingest multi-entity datasets from HDFS
badge_df = spark.read.option("header", True).csv("hdfs://master:9000/stackoverflow/badges.csv")
comment_df = spark.read.option("header", True).csv("hdfs://master:9000/stackoverflow/comments_final.csv")
post_answer_df = spark.read.option("header", True).csv("hdfs://master:9000/stackoverflow/posts_answers_fixed.csv")
post_question_df = spark.read.option("header", True).csv("hdfs://master:9000/stackoverflow/posts_questions_cleaned1.csv")
post_tag_wiki_df = spark.read.option("header", True).csv("hdfs://master:9000/stackoverflow/posts_tag_wiki_clean.csv")
post_tag_wiki_excerpt_df = spark.read.option("header", True).csv("hdfs://master:9000/stackoverflow/posts_tag_wiki_excerpt_100PCT.csv")
user_df = spark.read.option("header", True).csv("hdfs://master:9000/stackoverflow/users_clean.csv")
tag_df = spark.read.option("header", True).csv("hdfs://master:9000/stackoverflow/tags.csv")
vote_df = spark.read.option("header", True).csv("hdfs://master:9000/stackoverflow/votes.csv")
post_links_df = spark.read.option("header", True).csv("hdfs://master:9000/stackoverflow/posts_links.csv")

And then we put the DataFrames into Delta Lake tables in the database.

python

# Materialize entities as Delta tables for optimized query performance
badge_df.write.format("delta").mode("overwrite").saveAsTable("stackoverflow.badges")
comment_df.write.format("delta").mode("overwrite").saveAsTable("stackoverflow.comments")
post_answer_df.write.format("delta").mode("overwrite").saveAsTable("stackoverflow.posts_answers")
post_question_df.write.format("delta").mode("overwrite").saveAsTable("stackoverflow.posts_questions")
post_tag_wiki_df.write.format("delta").mode("overwrite").saveAsTable("stackoverflow.posts_tag_wiki")

# Continuing with excerpts, users, tags, votes, and links
post_tag_wiki_excerpt_df.write.format("delta").mode("overwrite").saveAsTable("stackoverflow.posts_tag_wiki_excerpt")
user_df.write.format("delta").mode("overwrite").saveAsTable("stackoverflow.users")
tag_df.write.format("delta").mode("overwrite").saveAsTable("stackoverflow.tags")
vote_df.write.format("delta").mode("overwrite").saveAsTable("stackoverflow.votes")
post_links_df.write.format("delta").mode("overwrite").saveAsTable("stackoverflow.posts_links")

Define Nodes

Next up, we will use the PuppyGraph browser GUI to import the data into the graph. First, we will create the Badges vertex.

Then we will create the Comments vertex.

Next we will create the Post_answers vertex.

Next we will create the Post_questions vertex.

Next we will create the Post_tag_wiki vertex.

Next we will create the Post_tag_wiki_excerpt vertex.

Next we will create the Tags vertex.

Next we will create the Users vertex.

Next we will create the Votes vertex.

Define Edges

Next we will create the edges. We create a separate Delta Lake table for each edge type using Spark, so that we can create a one-to-one mapping from tables to PuppyGraph.

First we will create the OWNED_BY edges from Post_questions to Users.

python

from pyspark.sql import functions as F

pq = (spark.table("stackoverflow.posts_questions")
    .select(F.trim(F.col("id")).alias("from_id"),
        F.trim(F.col("owner_user_id")).alias("to_id")))

u = (spark.table("stackoverflow.users")
    .select(F.trim(F.col("id")).alias("user_id"))
    .dropna()
    .dropDuplicates(["user_id"]))

owned_by_edges = (pq
    .filter(F.col("to_id").isNotNull())
    .filter(F.col("to_id") != "")
    .filter(F.col("to_id") != "0")
    .join(u, F.col("to_id") == F.col("user_id"), "inner")
    .select("from_id", "to_id"))

print("Owned_by edges:", owned_by_edges.count())
owned_by_edges.write.format("delta").mode("overwrite").saveAsTable("stackoverflow.edges_owned_by")

Next, we will create ANSWERS_BY edges from Post_answer to Users.

python

pa = (spark.table("stackoverflow.posts_answers")
    .select(F.trim(F.col("id")).alias("from_id"),
        F.trim(F.col("owner_user_id")).alias("to_id")))

answers_by_edges = (pa
    .filter(F.col("to_id").isNotNull())
    .filter(F.col("to_id") != "")
    .filter(F.col("to_id") != "0")
    .join(u, F.col("to_id") == F.col("user_id"), "inner")
    .select("from_id", "to_id"))

print("Answers_by edges:", answers_by_edges.count())

answers_by_edges.write.format("delta").mode("overwrite").saveAsTable("stackoverflow.edges_answers_by")

Next, we will create the CREATED_BY edges from Post_tag_wiki to Users.

python

ptw = (spark.table("stackoverflow.posts_tag_wiki")
    .select(F.trim(F.col("id")).alias("from_id"),
        F.trim(F.col("owner_user_id")).alias("to_id")))

created_by_wiki_edges = (ptw
    .filter(F.col("to_id").isNotNull())
    .filter(F.col("to_id") != "")
    .filter(F.col("to_id") != "0")
    .join(u, F.col("to_id") == F.col("user_id"), "inner")
    .select("from_id", "to_id"))

print("Created_by_wiki edges:", created_by_wiki_edges.count())

created_by_wiki_edges.write.format("delta").mode("overwrite").saveAsTable("stackoverflow.edges_created_by_wiki")

Next, we will create the CREATED_BY edges from Post_tag_wiki_excerpt to Users.

python

twe = (spark.table("stackoverflow.posts_tag_wiki_excerpt")
    .select(F.trim(F.col("id")).alias("from_id"),
        F.trim(F.col("owner_user_id")).alias("to_id")))

edges_created_by_excerpt = (twe
    .filter(F.col("to_id").isNotNull())
    .filter(F.col("to_id") != "")
    .filter(F.col("to_id") != "0")
    .join(u, F.col("to_id") == F.col("user_id"), "inner")
    .select("from_id", "to_id"))

print("Created_by_excerpt edges:", edges_created_by_excerpt.count())

edges_created_by_excerpt.write.format("delta").mode("overwrite").saveAsTable("stackoverflow.edges_created_by_excerpt")

Next, we will create the LAST_EDITED_BY edges from Post_question to Users.

python

pq_last = (spark.table("stackoverflow.posts_questions")
    .select(F.trim(F.col("id")).alias("from_id"),
        F.trim(F.col("last_editor_user_id")).alias("to_id")))

edges_last_edited_by_question = (pq_last
    .filter(F.col("to_id").isNotNull())
    .filter(F.col("to_id") != "")
    .filter(F.col("to_id") != "0")
    .join(u, F.col("to_id") == F.col("user_id"), "inner")
    .select("from_id", "to_id"))

print("Last_edited_by_question edges:", edges_last_edited_by_question.count())

edges_last_edited_by_question.write.format("delta").mode("overwrite").saveAsTable("stackoverflow.edges_last_edited_by_question")

Next, we will create the LAST_EDITED_BY edges from Post_answer to Users.

python

pa_last = (spark.table("stackoverflow.posts_answers")
    .select(F.trim(F.col("id")).alias("from_id"),
        F.trim(F.col("last_editor_user_id")).alias("to_id")))

edges_last_edited_by_answer = (pa_last
    .filter(F.col("to_id").isNotNull())
    .filter(F.col("to_id") != "")
    .filter(F.col("to_id") != "0")
    .join(u, F.col("to_id") == F.col("user_id"), "inner")
    .select("from_id", "to_id"))

print("Last_edited_by_answer edges:", edges_last_edited_by_answer.count())

edges_last_edited_by_answer.write.format("delta").mode("overwrite").saveAsTable("stackoverflow.edges_last_edited_by_answer")

Next, we will create the LAST_EDITED_BY edges from Post_tag_wiki to Users.

python

ptw_last = (spark.table("stackoverflow.posts_tag_wiki")
    .select(F.trim(F.col("id")).alias("from_id"),
        F.trim(F.col("last_editor_user_id")).alias("to_id")))

edges_last_edited_by_wiki = (ptw_last
    .filter(F.col("to_id").isNotNull())
    .filter(F.col("to_id") != "")
    .filter(F.col("to_id") != "0")
    .join(u, F.col("to_id") == F.col("user_id"), "inner")
    .select("from_id", "to_id"))

print("Last_edited_by_wiki edges:", edges_last_edited_by_wiki.count())

edges_last_edited_by_wiki.write.format("delta").mode("overwrite").saveAsTable("stackoverflow.edges_last_edited_by_wiki")

Next, we will create the LAST_EDITED_BY edges from Post_tag_wiki_excerpt to Users.

python

twe_last = (spark.table("stackoverflow.posts_tag_wiki_excerpt")
    .select(F.trim(F.col("id")).alias("from_id"),
        F.trim(F.col("last_editor_user_id")).alias("to_id")))

edges_last_edited_by_excerpt = (twe_last
    .filter(F.col("to_id").isNotNull())
    .filter(F.col("to_id") != "")
    .filter(F.col("to_id") != "0")
    .join(u, F.col("to_id") == F.col("user_id"), "inner")
    .select("from_id", "to_id"))

print("Last_edited_by_excerpt edges:", edges_last_edited_by_excerpt.count())

edges_last_edited_by_excerpt.write.format("delta").mode("overwrite").saveAsTable("stackoverflow.edges_last_edited_by_excerpt")

Next, we will create the EARNS edges from Users to Badges.

python

bdg = (spark.table("stackoverflow.badges")
    .select(F.trim(F.col("user_id")).alias("from_id"),
        F.trim(F.col("id")).alias("to_id")))

edges_earns = (bdg
    .filter(F.col("from_id").isNotNull())
    .filter(F.col("from_id") != "")
    .filter(F.col("from_id") != "0")
    .join(u, F.col("from_id") == F.col("user_id"), "inner")
    .select("from_id", "to_id"))
    .dropDuplicates(["from_id", "to_id"])

print("Earns edges:", edges_earns.count())

edges_earns.write.format("delta").mode("overwrite").saveAsTable("stackoverflow.edges_earns")

Next, we will create the ANSWERS_TO edges from Post_answer to Post_question.

python

ans_to = (spark.table("stackoverflow.posts_answers")
    .select(F.trim(F.col("id")).alias("from_id"),
        F.trim(F.col("parent_id")).alias("to_id")))

edges_answers_to = (ans_to
    .filter(F.col("to_id").isNotNull())
    .filter(F.col("to_id") != "")
    .filter(F.col("to_id") != "0")
    .select("from_id", "to_id"))

print("Answers_to edges:", edges_answers_to.count())

edges_answers_to.write.format("delta").mode("overwrite").saveAsTable("stackoverflow.edges_answers_to")

Next, we will create the ACCEPTS edges from Post_question to Post_answer.

python

acc = (spark.table("stackoverflow.posts_questions")
    .select(F.trim(F.col("id")).alias("from_id"),
        F.trim(F.col("accepted_answer_id")).alias("to_id")))

edges_accepts = (acc
    .filter(F.col("to_id").isNotNull())
    .filter(F.col("to_id") != "")
    .filter(F.col("to_id") != "0")
    .select("from_id", "to_id"))

print("Accepts edges:", edges_accepts.count())

edges_accepts.write.format("delta").mode("overwrite").saveAsTable("stackoverflow.edges_accepts")

Next, we will create the COMMENTED_BY edges from Comments to Users.

python

cm_user = (spark.table("stackoverflow.comments")
    .select(F.trim(F.col("id")).alias("from_id"),
        F.trim(F.col("user_id")).alias("to_id")))

edges_commented_by = (cm_user
    .filter(F.col("to_id").isNotNull())
    .filter(F.col("to_id") != "")
    .filter(F.col("to_id") != "0")
    .join(u, F.col("to_id") == F.col("user_id"), "inner")
    .select("from_id", "to_id"))

print("Commented_by edges:", edges_commented_by.count())

edges_commented_by.write.format("delta").mode("overwrite").saveAsTable("stackoverflow.edges_commented_by")

Next, we will create the ON_QUESTION edges from Comments to Post_question.

python

u_ids = (spark.table("stackoverflow.users")
    .select(F.trim(F.col("id")).alias("user_id"))
    .filter(F.col("user_id").isNotNull())
    .filter(F.col("user_id") != "")
    .filter(F.col("user_id") != "0")
    .dropDuplicates(["user_id"]))

q_ids = (spark.table("stackoverflow.posts_questions")
    .select(F.trim(F.col("id")).alias("question_id"))
    .filter(F.col("question_id").isNotNull())
    .filter(F.col("question_id") != "")
    .filter(F.col("question_id") != "0")
    .dropDuplicates(["question_id"]))

a_ids = (spark.table("stackoverflow.posts_answers")
    .select(F.trim(F.col("id")).alias("answer_id"))
    .filter(F.col("answer_id").isNotNull())
    .filter(F.col("answer_id") != "")
    .filter(F.col("answer_id") != "0")
    .dropDuplicates(["answer_id"]))

tw_ids = (spark.table("stackoverflow.posts_tag_wiki")
    .select(F.trim(F.col("id")).alias("tag_wiki_id"))
    .filter(F.col("tag_wiki_id").isNotNull())
    .filter(F.col("tag_wiki_id") != "")
    .filter(F.col("tag_wiki_id") != "0")
    .dropDuplicates(["tag_wiki_id"]))

twe_ids = (spark.table("stackoverflow.posts_tag_wiki_excerpt")
    .select(F.trim(F.col("id")).alias("tag_wiki_excerpt_id"))
    .filter(F.col("tag_wiki_excerpt_id").isNotNull())
    .filter(F.col("tag_wiki_excerpt_id") != "")
    .filter(F.col("tag_wiki_excerpt_id") != "0")
    .dropDuplicates(["tag_wiki_excerpt_id"]))

b_ids = (spark.table("stackoverflow.badges")
    .select(F.trim(F.col("id")).alias("badge_id"))
    .filter(F.col("badge_id").isNotNull())
    .filter(F.col("badge_id") != "")
    .filter(F.col("badge_id") != "0")
    .dropDuplicates(["badge_id"]))

c_ids = (spark.table("stackoverflow.comments")
    .select(F.trim(F.col("id")).alias("comment_id"))
    .filter(F.col("comment_id").isNotNull())
    .filter(F.col("comment_id") != "")
    .filter(F.col("comment_id") != "0")
    .dropDuplicates(["comment_id"]))

v_ids = (spark.table("stackoverflow.votes")
    .select(F.trim(F.col("id")).alias("vote_id"))
    .filter(F.col("vote_id").isNotNull())
    .filter(F.col("vote_id") != "")
    .filter(F.col("vote_id") != "0")
    .dropDuplicates(["vote_id"]))

def valid(colname: str):
    return (F.col(colname).isNotNull()) & (F.col(colname) != "") & (F.col(colname) != "0")

cm_on_src = (spark.table("stackoverflow.comments")
    .select(F.trim(F.col("id")).alias("from_id"),
        F.trim(F.col("post_id")).alias("to_id"))
    .filter(valid("to_id")))

edges_on_question = (cm_on_src
    .join(c_ids, F.col("from_id") == F.col("comment_id"), "inner")
    .join(q_ids, F.col("to_id") == F.col("question_id"), "inner")
    .select("from_id", "to_id")
)

print("On_question:", edges_on_question.count())

edges_on_question.write.format("delta").mode("overwrite").saveAsTable("stackoverflow.edges_on_question")

Next, we will create the ON_ANSWER edges from Comments to Post_answer.

python

edges_on_answer = (cm_on_src
    .join(c_ids, F.col("from_id") == F.col("comment_id"), "inner")
    .join(a_ids, F.col("to_id") == F.col("answer_id"), "inner")
    .select("from_id", "to_id"))

print("On_answer:", edges_on_answer.count())

edges_on_answer.write.format("delta").mode("overwrite").saveAsTable("stackoverflow.edges_on_answer")

Next, we will create the VOTE_ON edges from Votes to Post_question.

python

votes_src = (spark.table("stackoverflow.votes")
    .select(F.trim(F.col("id")).alias("from_id"),
        F.trim(F.col("post_id")).alias("to_id"))
    .filter(valid("to_id")))

edges_vote_on_question = (votes_src
    .join(v_ids, F.col("from_id") == F.col("vote_id"), "inner")
    .join(q_ids, F.col("to_id") == F.col("question_id"), "inner")
    .select("from_id", "to_id"))

edges_vote_on_question.write.format("delta").mode("overwrite").saveAsTable("stackoverflow.edges_vote_on_question")

Next, we will create the VOTE_ON edges from Votes to Post_answer.

python

edges_vote_on_answer = (votes_src
    .join(v_ids, F.col("from_id") == F.col("vote_id"), "inner")
    .join(a_ids, F.col("to_id") == F.col("answer_id"), "inner")
    .select("from_id", "to_id"))
edges_vote_on_answer.write.format("delta").mode("overwrite").saveAsTable("stackoverflow.edges_vote_on_answer")

Next, we will create the VOTE_ON edges from Votes to Comments.

python

edges_vote_on_comment = (votes_src
    .join(v_ids, F.col("from_id") == F.col("vote_id"), "inner")
    .join(c_ids, F.col("to_id") == F.col("comment_id"), "inner")
    .select("from_id", "to_id"))

print("Vote_on_comment:", edges_vote_on_comment.count())

edges_vote_on_comment.write.format("delta").mode("overwrite").saveAsTable("stackoverflow.edges_vote_on_comment")

Next, we will create the TAG_TO edges from Tag to Post_question.

python

def valid(colname: str):
    return (F.col(colname).isNotNull()) & (F.col(colname) != "") & (F.col("colname") != "0")

tag_ids = (spark.table("stackoverflow.tags")
    .select(F.trim(F.col("id")).alias("tag_id"),
        F.trim(F.col("tag_name")).alias("tag_name"))
    .filter(valid("tag_id"))
    .dropDuplicates(["tag_id"]))

tag_name_map = (spark.table("stackoverflow.tags")
    .select(F.lower(F.trim(F.col("tag_name"))).alias("tag_name_lc"),
        F.trim(F.col("id")).alias("tag_id"))
    .filter(F.col("tag_name_lc").isNotNull())
    .filter(F.col("tag_name_lc") != "")
    .filter(valid("tag_id"))
    .dropDuplicates(["tag_name_lc"]))

question_ids = (spark.table("stackoverflow.posts_questions")
    .select(F.trim(F.col("id")).alias("question_id"))
    .filter(valid("question_id"))
    .dropDuplicates(["question_id"]))

tag_wiki_ids = (spark.table("stackoverflow.posts_tag_wiki")
    .select(F.trim(F.col("id")).alias("tag_wiki_id"))
    .filter(valid("tag_wiki_id"))
    .dropDuplicates(["tag_wiki_id"]))

tag_wiki_excerpt_ids = (spark.table("stackoverflow.posts_tag_wiki_excerpt")
    .select(F.trim(F.col("id")).alias("tag_wiki_excerpt_id"))
    .filter(valid("tag_wiki_excerpt_id"))
    .dropDuplicates(["tag_wiki_excerpt_id"]))

pq_tags = (spark.table("stackoverflow.posts_questions")
    .select(F.trim(F.col("id")).alias("question_id"),
        F.col("tags").alias("tags_raw"))
    .filter(valid("question_id"))
    .filter(F.col("tags_raw").isNotNull())
    .filter(F.trim(F.col("tags_raw")) != ""))

pq_exploded = (pq_tags
    .withColumn(
        "tags_pipe",
        F.regexp_replace(
            F.regexp_replace(F.col("tags_raw"), r"><", "|"),
            r"[<>]",
            ""
        )
    )
    .withColumn("tag_name_lc", F.explode(F.split(F.lower(F.col("tags_pipe")), r"|")))
    .withColumn("tag_name_lc", F.trim(F.col("tag_name_lc")))
    .filter(F.col("tag_name_lc") != "")
    .select("question_id", "tag_name_lc")
)

edges_tag_to = (pq_exploded
    .join(tag_name_map, on="tag_name_lc", how="inner")
    .join(question_ids, on="question_id", how="inner")
    .select(F.col("tag_id").alias("from_id"),
        F.col("question_id").alias("to_id"))
    .dropDuplicates(["from_id", "to_id"])
)

print("Tag_to edges:", edges_tag_to.count())

edges_tag_to.write.format("delta").mode("overwrite").saveAsTable("stackoverflow.edges_tag_to")

Next, we will create the HAS_WIKI edges from Tag to Post_tag_wiki.

python

has_wiki_src = (spark.table("stackoverflow.tags")
    .select(F.trim(F.col("id")).alias("from_id"),
        F.trim(F.col("wiki_post_id")).alias("to_id"))
    .filter(valid("from_id"))
    .filter(valid("to_id")))

edges_has_wiki = (has_wiki_src
    .join(tag_ids.select("tag_id"), F.col("from_id") == F.col("tag_id"), "inner")
    .join(tag_wiki_ids, F.col("to_id") == F.col("tag_wiki_id"), "inner")
    .select("from_id", "to_id")
    .dropDuplicates(["from_id", "to_id"])
)

print("Has_wiki edges:", edges_has_wiki.count())

edges_has_wiki.write.format("delta").mode("overwrite").saveAsTable("stackoverflow.edges_has_wiki")

Next, we will create the HAS_WIKI edges from Tag to Post_tag_wiki_excerpt.

python

has_wiki_excerpt_src = (spark.table("stackoverflow.tags")
    .select(F.trim(F.col("id")).alias("from_id"),
        F.trim(F.col("excerpt_post_id")).alias("to_id"))
    .filter(valid("from_id"))
    .filter(valid("to_id")))

edges_has_wiki_excerpt = (has_wiki_excerpt_src
    .join(tag_ids.select("tag_id"), F.col("from_id") == F.col("tag_id"), "inner")
    .join(tag_wiki_excerpt_ids, F.col("to_id") == F.col("tag_wiki_excerpt_id"), "inner")
    .select("from_id", "to_id")
    .dropDuplicates(["from_id", "to_id"])
)

print("Has_wiki_excerpt edges:", edges_has_wiki_excerpt.count())

edges_has_wiki_excerpt.write.format("delta").mode("overwrite").saveAsTable("stackoverflow.edges_has_wiki_excerpt")

Next, we will create the LINK_TO edges from Post_question to Post_question.

python

links_src = (spark.table("stackoverflow.posts_links")
    .select(F.trim(F.col("post_id")).alias("from_id"),
        F.trim(F.col("related_post_id")).alias("to_id"))
    .filter(valid("from_id"))
    .filter(valid("to_id")))

edges_link_to = (links_src
    .join(question_ids.withColumnRenamed("question_id", "q_from"),
        F.col("from_id") == F.col("q_from"), "inner")
    .join(question_ids.withColumnRenamed("question_id", "q_to"),
        F.col("to_id") == F.col("q_to"), "inner")
    .select("from_id", "to_id")
    .dropDuplicates(["from_id", "to_id"])
)

print("Link_to edges:", edges_link_to.count())

edges_link_to.write.format("delta").mode("overwrite").saveAsTable("stackoverflow.edges_link_to")

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.

cypher

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;

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.

cypher

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;

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.

cypher

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;

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.

cypher

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;

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.

cypher

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;

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.

cypher

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;

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.

cypher

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;

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.

cypher

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;

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.

cypher

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;

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.

cypher

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;

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;