1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for those who study, teach, research and apply economics and econometrics

-- 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,
COUNT(DISTINCT a.Id) AS Answers,

-- 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

fork query

edited feb 11

created feb 11 jon.doe43784