Answer stats

1

Please login or register to vote for this query.

(click on this box to dismiss)

Ask Ubuntu

Q&A for Ubuntu users and developers

DECLARE @UserId int = ##UserId##
;

with answers as (
  SELECT
      question.title,
      answer.Id,
      MAX(datediff(day, answer.CreationDate, getdate())) AS age,
      SUM(case when Votes.VoteTypeId = 2 then 1
               when Votes.VoteTypeId = 3 then -1
          end) AS Score,
      SUM(case when Votes.VoteTypeId = 2 then 1 end) AS Upvotes,
      SUM(case when Votes.VoteTypeId = 3 then 1 end) AS Downvotes,
      SUM(case when Votes.VoteTypeId = 1 then 1 end) AS Accepted,
      MAX(question.ViewCount) as ViewCount
  FROM Posts answer
  JOIN Posts question on answer.ParentId = question.id
  LEFT JOIN Votes ON Votes.PostId = answer.Id
  WHERE answer.postTypeId = 2 --answer
    AND answer.OwnerUserId = @UserId
    AND answer.CommunityOwnedDate IS NULL
  GROUP BY question.title, answer.id 
)
/*select tags.TagName, answers.*
from answers
join PostTags on answer.ParentId = PostTags.PostId
join Tags ON PostTags.TagId = Tags.id*/
select * from answers
ORDER BY Score DESC

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results