Skip to main content
Meta Stack Exchange

Return to Question

deleted 44 characters in body; edited tags
Source Link
Glorfindel Mod
Glorfindel Mod
  • 255.6k
  • 61
  • 638
  • 1.3k

I am trying to pull a list of all users who have commented on an answer and tie it back to their User profile, if one exists. However, not all Comments table records have a UserId associated with them in the Stack Exchange Data Explorer result set. Those that do not have a UserId, have a UserDisplayName field instead. What I am curious about is how the User table information is related to the UserDisplayName field. Are these users who have not created an account on Stack Exchange? If they later create an account, is there a way to tie them back together?

I am aware that you cannot ask new questions without creating an account based on this link https://stackoverflow.com/help/why-registerthis link, but I am curious if there is a way / how to tie back the anonymous users that answer questions or comment on posts.

To contextualize why I need this, I am interested in exploring the influence of users with the answers on Stack Exchange using comments for academic research.

Here is the query I am running on SEDE:

CREATE TABLE #questions (
QuestionId INT)

CREATE TABLE #answers (
AnswerId INT
,questionId INT)

INSERT #questions
SELECT DISTINCT TOP 1000 Posts.Id
FROM Posts
WHERE PostTypeId = 1


INSERT #answers
SELECT DISTINCT 
    Posts.Id
    ,q.questionId
FROM #questions q
INNER JOIN Posts
    ON Posts.ParentID = q.QuestionId
        AND Posts.PostTypeId = 2


SELECT 'https://stackoverflow.com/questions/' + CAST(a.questionId AS CHAR(20))
    SO_Link_Question
    ,'https://stackoverflow.com/questions/' + CAST(a.answerId AS CHAR(20))
    SO_Link_Answer
    ,UserId
    ,UserDisplayName
FROM Comments c
INNER JOIN #answers a
    ON a.AnswerId = c.PostId
ORDER BY a.questionID, a.answerId

I am trying to pull a list of all users who have commented on an answer and tie it back to their User profile, if one exists. However, not all Comments table records have a UserId associated with them in the Stack Exchange Data Explorer result set. Those that do not have a UserId, have a UserDisplayName field instead. What I am curious about is how the User table information is related to the UserDisplayName field. Are these users who have not created an account on Stack Exchange? If they later create an account, is there a way to tie them back together?

I am aware that you cannot ask new questions without creating an account based on this link https://stackoverflow.com/help/why-register, but I am curious if there is a way / how to tie back the anonymous users that answer questions or comment on posts.

To contextualize why I need this, I am interested in exploring the influence of users with the answers on Stack Exchange using comments for academic research.

Here is the query I am running on SEDE:

CREATE TABLE #questions (
QuestionId INT)

CREATE TABLE #answers (
AnswerId INT
,questionId INT)

INSERT #questions
SELECT DISTINCT TOP 1000 Posts.Id
FROM Posts
WHERE PostTypeId = 1


INSERT #answers
SELECT DISTINCT 
    Posts.Id
    ,q.questionId
FROM #questions q
INNER JOIN Posts
    ON Posts.ParentID = q.QuestionId
        AND Posts.PostTypeId = 2


SELECT 'https://stackoverflow.com/questions/' + CAST(a.questionId AS CHAR(20))
    SO_Link_Question
    ,'https://stackoverflow.com/questions/' + CAST(a.answerId AS CHAR(20))
    SO_Link_Answer
    ,UserId
    ,UserDisplayName
FROM Comments c
INNER JOIN #answers a
    ON a.AnswerId = c.PostId
ORDER BY a.questionID, a.answerId

I am trying to pull a list of all users who have commented on an answer and tie it back to their User profile, if one exists. However, not all Comments table records have a UserId associated with them in the Stack Exchange Data Explorer result set. Those that do not have a UserId, have a UserDisplayName field instead. What I am curious about is how the User table information is related to the UserDisplayName field. Are these users who have not created an account on Stack Exchange? If they later create an account, is there a way to tie them back together?

I am aware that you cannot ask new questions without creating an account based on this link, but I am curious if there is a way / how to tie back the anonymous users that answer questions or comment on posts.

To contextualize why I need this, I am interested in exploring the influence of users with the answers on Stack Exchange using comments for academic research.

Here is the query I am running on SEDE:

CREATE TABLE #questions (
QuestionId INT)

CREATE TABLE #answers (
AnswerId INT
,questionId INT)

INSERT #questions
SELECT DISTINCT TOP 1000 Posts.Id
FROM Posts
WHERE PostTypeId = 1


INSERT #answers
SELECT DISTINCT 
    Posts.Id
    ,q.questionId
FROM #questions q
INNER JOIN Posts
    ON Posts.ParentID = q.QuestionId
        AND Posts.PostTypeId = 2


SELECT 'https://stackoverflow.com/questions/' + CAST(a.questionId AS CHAR(20))
    SO_Link_Question
    ,'https://stackoverflow.com/questions/' + CAST(a.answerId AS CHAR(20))
    SO_Link_Answer
    ,UserId
    ,UserDisplayName
FROM Comments c
INNER JOIN #answers a
    ON a.AnswerId = c.PostId
ORDER BY a.questionID, a.answerId
deleted 10 characters in body
Source Link
Glorfindel Mod
Glorfindel Mod
  • 255.6k
  • 61
  • 638
  • 1.3k

I am trying to pull a list of all users who have commented on an answer and tie it back to their User profile, if one exists. However, not all Comments table records have a UserId associated with them in the Stack Exchange Data Explorer result set. Those that do not have a UserId, have a UserDisplayName field instead. What I am curious about is how the User table information is related to the UserDisplayName field. Are these users who have not created an account on Stack Exchange? If they later create an account, is there a way to tie them back together?

I am aware that there is not an ability toyou cannot ask new questions without creating an account based on this link https://stackoverflow.com/help/why-register, but I am curious if there is a way / how to tie back the anonanonymous users that answer questions or comment on posts.

To contextualize why I need this, I am interested in exploring the influence of users with the answers on stack exchangeStack Exchange using comments for academic research.

Here is the query I am running on SEDE:

CREATE TABLE #questions (
QuestionId INT)

CREATE TABLE #answers (
AnswerId INT
,questionId INT)

INSERT #questions
SELECT DISTINCT TOP 1000 Posts.Id
FROM Posts
WHERE PostTypeId = 1


INSERT #answers
SELECT DISTINCT 
    Posts.Id
    ,q.questionId
FROM #questions q
INNER JOIN Posts
    ON Posts.ParentID = q.QuestionId
        AND Posts.PostTypeId = 2


SELECT 'https://stackoverflow.com/questions/' + CAST(a.questionId AS CHAR(20))
    SO_Link_Question
    ,'https://stackoverflow.com/questions/' + CAST(a.answerId AS CHAR(20))
    SO_Link_Answer
    ,UserId
    ,UserDisplayName
FROM Comments c
INNER JOIN #answers a
    ON a.AnswerId = c.PostId
ORDER BY a.questionID, a.answerId

I am trying to pull a list of all users who have commented on an answer and tie it back to their User profile, if one exists. However, not all Comments table records have a UserId associated with them in the Stack Exchange Data Explorer result set. Those that do not have a UserId, have a UserDisplayName field instead. What I am curious about is how the User table information is related to the UserDisplayName field. Are these users who have not created an account on Stack Exchange? If they later create an account, is there a way to tie them back together?

I am aware that there is not an ability to ask new questions without creating an account based on this link https://stackoverflow.com/help/why-register, but I am curious if there is a way / how to tie back the anon users that answer questions or comment on posts.

To contextualize why I need this, I am interested in exploring the influence of users with the answers on stack exchange using comments for academic research.

Here is the query I am running on SEDE

CREATE TABLE #questions (
QuestionId INT)

CREATE TABLE #answers (
AnswerId INT
,questionId INT)

INSERT #questions
SELECT DISTINCT TOP 1000 Posts.Id
FROM Posts
WHERE PostTypeId = 1


INSERT #answers
SELECT DISTINCT 
    Posts.Id
    ,q.questionId
FROM #questions q
INNER JOIN Posts
    ON Posts.ParentID = q.QuestionId
        AND Posts.PostTypeId = 2


SELECT 'https://stackoverflow.com/questions/' + CAST(a.questionId AS CHAR(20))
    SO_Link_Question
    ,'https://stackoverflow.com/questions/' + CAST(a.answerId AS CHAR(20))
    SO_Link_Answer
    ,UserId
    ,UserDisplayName
FROM Comments c
INNER JOIN #answers a
    ON a.AnswerId = c.PostId
ORDER BY a.questionID, a.answerId

I am trying to pull a list of all users who have commented on an answer and tie it back to their User profile, if one exists. However, not all Comments table records have a UserId associated with them in the Stack Exchange Data Explorer result set. Those that do not have a UserId, have a UserDisplayName field instead. What I am curious about is how the User table information is related to the UserDisplayName field. Are these users who have not created an account on Stack Exchange? If they later create an account, is there a way to tie them back together?

I am aware that you cannot ask new questions without creating an account based on this link https://stackoverflow.com/help/why-register, but I am curious if there is a way / how to tie back the anonymous users that answer questions or comment on posts.

To contextualize why I need this, I am interested in exploring the influence of users with the answers on Stack Exchange using comments for academic research.

Here is the query I am running on SEDE:

CREATE TABLE #questions (
QuestionId INT)

CREATE TABLE #answers (
AnswerId INT
,questionId INT)

INSERT #questions
SELECT DISTINCT TOP 1000 Posts.Id
FROM Posts
WHERE PostTypeId = 1


INSERT #answers
SELECT DISTINCT 
    Posts.Id
    ,q.questionId
FROM #questions q
INNER JOIN Posts
    ON Posts.ParentID = q.QuestionId
        AND Posts.PostTypeId = 2


SELECT 'https://stackoverflow.com/questions/' + CAST(a.questionId AS CHAR(20))
    SO_Link_Question
    ,'https://stackoverflow.com/questions/' + CAST(a.answerId AS CHAR(20))
    SO_Link_Answer
    ,UserId
    ,UserDisplayName
FROM Comments c
INNER JOIN #answers a
    ON a.AnswerId = c.PostId
ORDER BY a.questionID, a.answerId
Source Link
William Ledbetter
William Ledbetter
  • 233
  • 1
  • 5

What is the difference between DisplayName and Id on the Stack Exchange Data Explorer Users table?

I am trying to pull a list of all users who have commented on an answer and tie it back to their User profile, if one exists. However, not all Comments table records have a UserId associated with them in the Stack Exchange Data Explorer result set. Those that do not have a UserId, have a UserDisplayName field instead. What I am curious about is how the User table information is related to the UserDisplayName field. Are these users who have not created an account on Stack Exchange? If they later create an account, is there a way to tie them back together?

I am aware that there is not an ability to ask new questions without creating an account based on this link https://stackoverflow.com/help/why-register, but I am curious if there is a way / how to tie back the anon users that answer questions or comment on posts.

To contextualize why I need this, I am interested in exploring the influence of users with the answers on stack exchange using comments for academic research.

Here is the query I am running on SEDE

CREATE TABLE #questions (
QuestionId INT)

CREATE TABLE #answers (
AnswerId INT
,questionId INT)

INSERT #questions
SELECT DISTINCT TOP 1000 Posts.Id
FROM Posts
WHERE PostTypeId = 1


INSERT #answers
SELECT DISTINCT 
    Posts.Id
    ,q.questionId
FROM #questions q
INNER JOIN Posts
    ON Posts.ParentID = q.QuestionId
        AND Posts.PostTypeId = 2


SELECT 'https://stackoverflow.com/questions/' + CAST(a.questionId AS CHAR(20))
    SO_Link_Question
    ,'https://stackoverflow.com/questions/' + CAST(a.answerId AS CHAR(20))
    SO_Link_Answer
    ,UserId
    ,UserDisplayName
FROM Comments c
INNER JOIN #answers a
    ON a.AnswerId = c.PostId
ORDER BY a.questionID, a.answerId