Being curious to see which tags on this site get the most attention, I developed a Stack Exchange Data Explorer query:
WITH CanonicalTags AS (
SELECT Id, TagName, Id AS AliasId, TagName AS Alias
FROM Tags
WHERE NOT EXISTS (
SELECT Id
FROM TagSynonyms
WHERE SourceTagName=TagName AND ApprovedByUserId IS NOT NULL
)
UNION
SELECT Tags.Id, Tags.TagName, Alias.Id AS AliasId, Alias.TagName
FROM Tags
INNER JOIN TagSynonyms
ON TargetTagName=TagName
INNER JOIN Tags AS Alias
ON Alias.TagName=SourceTagName
WHERE ApprovedByUserId IS NOT NULL
), TagQuestionStats AS (
SELECT Tags.Id AS Id
, Tags.TagName AS TagName
, AVG(CAST(Questions.Score AS DECIMAL)) AS AvgScore
, STDEV(Questions.Score) AS StdDevScore
, COUNT(Questions.Id) AS N
FROM CanonicalTags AS Tags
INNER JOIN PostTags
ON PostTags.TagId=Tags.AliasId
INNER JOIN Posts AS Questions
ON Questions.Id=PostTags.PostId
GROUP BY Tags.Id, Tags.TagName
), TagAnswerStats AS (
SELECT Tags.Id AS Id
, Tags.TagName AS TagName
, AVG(CAST(Answers.Score AS DECIMAL)) AS AvgScore
, STDEV(Answers.Score) AS StdDevScore
, COUNT(Answers.Id) AS N
FROM CanonicalTags AS Tags
INNER JOIN PostTags
ON PostTags.TagId=Tags.AliasId
INNER JOIN Posts AS Questions
ON Questions.Id=PostTags.PostId
INNER JOIN Posts AS Answers
ON Answers.ParentId=Questions.Id
WHERE
Answers.PostTypeId=2
GROUP BY Tags.Id, Tags.TagName
)
SELECT '<' + QStats.TagName + '>' AS [TagName]
, ROUND(QStats.AvgScore, 2) AS [Avg Qst Score]
, ROUND(QStats.StdDevScore, 2) AS [Qst Score Std Dev]
, QStats.N AS [# Qst]
, ROUND(AStats.AvgScore, 2) AS [Avg Ans Score]
, ROUND(AStats.StdDevScore, 2) AS [Ans Score Std Dev]
, AStats.N AS [# Ans]
, ROUND(CAST(AStats.N AS DECIMAL) / CAST(QStats.N AS DECIMAL), 2) AS [Avg # Ans/Qst]
FROM
TagQuestionStats AS QStats
LEFT OUTER JOIN TagAnswerStats AS AStats
ON AStats.Id=QStats.Id
ORDER BY 5 DESC, 2 DESC, 7, 4, 1;
I'd like to ask:
- Some of the numbers don't round to two decimal places very well. Is that due to a bug in SQL Server, or am I using inappropriate rounding techniques? (The exact numbers don't matter; I'd much rather have a compact presentation.)
- Is there an easier way to canonicalize the tags?
- Any comments about my SQL formulation in general?
- Any suggestions for making the query itself more meaningful?