Avg Q/A per Tag


Please login or register to vote for this query.

(click on this box to dismiss)

Bitcoin Meta

Q&A about the site for Bitcoin crypto-currency enthusiasts

-- select the year and month from the creation date
SELECT YEAR(q.CreationDate) AS Year, MONTH(q.CreationDate) AS Month,

-- count the number of questions and answers for each post type
COUNT(DISTINCT q.Id) AS Questions,

-- calculate the average number of questions and answers per tag
COUNT(DISTINCT pt.TagId) AS CountOfTags,
(COUNT(DISTINCT q.Id)+0.0) / COUNT(DISTINCT pt.TagId) AS AvgQuestionsPerTag,
(COUNT(DISTINCT a.Id)+0.0) / COUNT(DISTINCT pt.TagId) AS AvgAnswersPerTag

-- join the posts table with itself to get the questions and answers
FROM Posts q
LEFT JOIN Posts a ON q.Id = a.ParentId

-- join the post tags table to get the tags
JOIN PostTags pt ON q.Id = pt.PostId

-- join the tags table to get the tag names
JOIN Tags t ON t.Id = pt.TagId

-- filter by the post type and the creation date since 2018
WHERE (q.PostTypeId = 1 -- questions
AND a.PostTypeId = 2) -- answers
AND q.CreationDate >= '2018-01-01'

-- group by the year and month
GROUP BY YEAR(q.CreationDate), MONTH(q.CreationDate)

-- order by the year and month
ORDER BY Year, Month

Enter Parameters

Switch to main site
loading Hold tight while we fetch your results