Q&A for professional and amateur woodworkers
DECLARE @startDate DATE = '2021-03-01'; DECLARE @endDate DATE = '2024-06-30'; WITH months AS ( SELECT ROW_NUMBER() OVER (ORDER BY DATEADD(MONTH, n - 1, @startDate)) AS month_number, DATEADD(MONTH, n - 1, @startDate) AS start_date, DATEADD(DAY, -1, DATEADD(MONTH, n, @startDate)) AS end_date FROM ( SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n FROM sys.objects a CROSS JOIN sys.objects b ) t WHERE n <= DATEDIFF(MONTH, @startDate, @endDate) + 1 ) SELECT m.month_number, m.start_date, m.end_date, COUNT(CASE WHEN p.PostTypeId = 1 THEN 1 END) AS total_questions_per_month, CASE WHEN COUNT(CASE WHEN p.PostTypeId = 1 THEN 1 END) = 0 THEN 0 ELSE SUM(CASE WHEN p.PostTypeId = 1 THEN p.Score END) * 1.0 / COUNT(CASE WHEN p.PostTypeId = 1 THEN 1 END) END AS average_score_questions_per_month, COUNT(CASE WHEN p.PostTypeId = 2 THEN 1 END) AS total_answers_per_month, CASE WHEN COUNT(CASE WHEN p.PostTypeId = 2 THEN 1 END) = 0 THEN 0 ELSE SUM(CASE WHEN p.PostTypeId = 2 THEN p.Score END) * 1.0 / COUNT(CASE WHEN p.PostTypeId = 2 THEN 1 END) END AS average_score_answers_per_month, CASE WHEN COUNT(CASE WHEN p.PostTypeId = 1 THEN 1 END) = 0 THEN 0 ELSE SUM(CASE WHEN p.PostTypeId = 1 THEN p.ViewCount END) * 1.0 / COUNT(CASE WHEN p.PostTypeId = 1 THEN 1 END) END AS average_views_per_question_per_month, CASE WHEN COUNT(CASE WHEN p.PostTypeId = 1 THEN 1 END) = 0 THEN 0 ELSE COUNT(CASE WHEN p.PostTypeId = 1 AND p.AcceptedAnswerId IS NOT NULL THEN 1 END) * 1.0 / COUNT(CASE WHEN p.PostTypeId = 1 THEN 1 END) END AS solution_ratio_per_month FROM months m JOIN Posts p ON p.CreationDate >= m.start_date AND p.CreationDate <= m.end_date GROUP BY m.month_number, m.start_date, m.end_date ORDER BY m.month_number ASC;