Q&A for scientists using computers to solve scientific problems
-- 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