Get User Info and Tags (Total answers and Accepted Answers)

0

Please login or register to vote for this query.

(click on this box to dismiss)

Returns the URL with the user's original inputted URL, the users reputation, number of answers posted, number of answers accepted and stats for supplied tags. For https://www.codersclan.net/ticket/671

Web Applications

Q&A for power users of web applications

-- Get User(s) Info
-- Returns the URL with the user's original inputted URL, the users reputation, number of answers posted, number of answers accepted.
-- For https://www.codersclan.net/ticket/659
/*
SELECT DISTINCT
  answer.OwnerUserId As [User Link],
  'http://stackoverflow.com/users/' + Cast(answer.OwnerUserId as nvarchar(10)) As [Permalink],
  Users.Reputation,
  question.tags as tgs,
  count(answer.id) AS [Total Answers],
  count(question.id) AS [Accepted Answers],
  ##Tags## as CountOf,
  ##Tags## as Accepted
FROM
  Posts answer
  LEFT OUTER JOIN Posts question ON question.AcceptedAnswerId = answer.id
  INNER JOIN Users ON answer.OwnerUserId = Users.id
WHERE
  answer.OwnerUserId in (##UserId##)
  AND
  answer.postTypeId = 2 -- 2 means that post is an answer
GROUP BY 
  answer.OwnerUserId, 
  Users.Reputation, question.tags*/

;WITH MyAnswers AS (
  SELECT distinct
    answer.Id as Id,
    answer.parentId as questionId,
    Accepted = CASE ISNULL(question.id,0) WHEN 0 THEN 0 ELSE 1 END
  FROM 
    Posts answer
    LEFT OUTER JOIN Posts question ON question.AcceptedAnswerId = answer.id
    LEFT OUTER JOIN Users ON answer.OwnerUserId = Users.id
  WHERE
    question.CreationDate > '2016-04-01'
    AND
    answer.postTypeId = 2
), AcceptedAnswers As(
SELECT 
    Tags.TagName,
    Count(tags.id) As TotalOfTag,
    SUM(MyAnswers.accepted) as AccepredOfTag
FROM 
  MyAnswers
    LEFT OUTER JOIN PostTags ON MyAnswers.questionId = PostTags.PostId
    LEFT OUTER JOIN Tags ON PostTags.TagId = Tags.id

GROUP BY
Tags.TagName) 

SELECT * FROM AcceptedAnswers

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results