0

Please login or register to vote for this query.

(click on this box to dismiss)

Total reputation and average reputation per answer for each tag.

Q&A for anime and manga fans

-- Reputation by Tag
-- Total reputation and average reputation per answer for each tag.
CREATE TABLE #TempReputation
(
   TagName nvarchar(25) COLLATE SQL_Latin1_General_CP1_CS_AS,
   Answers int,
   UpVotes int,
   DownVotes int,
   Reputation int,
   RepPerAnswer decimal(10,3)
)

INSERT INTO #TempReputation
SELECT t.TagName,
       COUNT(*) As Answers,
       0 AS UpVotes,
       0 AS DownVotes,
       0 AS Reputation,
       0.0 AS RepPerAnswer
FROM Posts AS q
INNER JOIN Posts AS a ON a.ParentId = q.Id
JOIN PostTags AS pt ON pt.PostId = q.Id
JOIN Tags AS t ON t.Id = pt.TagId
GROUP BY t.TagName

UPDATE #TempReputation
SET UpVotes = (SELECT COUNT(*) 
               FROM Posts AS q
               INNER JOIN Posts AS a ON a.ParentId = q.Id
               JOIN PostTags AS pt ON pt.PostId = q.Id
               JOIN Tags AS t ON t.Id = pt.TagId
               INNER JOIN Votes AS v ON v.PostId = a.Id
               WHERE v.VoteTypeId = 2 AND t.TagName = #TempReputation.TagName)

UPDATE #TempReputation
SET DownVotes = (SELECT COUNT(*) 
                 FROM Posts AS q
                 INNER JOIN Posts AS a ON a.ParentId = q.Id
                 JOIN PostTags AS pt ON pt.PostId = q.Id
                 JOIN Tags AS t ON t.Id = pt.TagId
                 INNER JOIN Votes AS v ON v.PostId = a.Id
                 WHERE v.VoteTypeId = 3 AND t.TagName = #TempReputation.TagName)

UPDATE #TempReputation
SET Reputation = (UpVotes * 10) - (DownVotes * 2)

UPDATE #TempReputation
SET RepPerAnswer = CONVERT(decimal(10,3),Reputation) / CONVERT(decimal(10,3),Answers)

SELECT * 
FROM #TempReputation
WHERE Answers > 75
ORDER BY RepPerAnswer DESC

fork query

created dec 16 10 jason.berkan