Q&A for Ubuntu users and developers
DECLARE @start datetime, @end datetime; DECLARE @Year int, @Month int; DECLARE @Topic nvarchar(max); SET @Year = ##Year:int##; -- replace 'Year' with the specific year SET @Month = ##Month:int##; -- replace 'Month' with the specific month SET @Topic = ##interestedTopic:string##; -- replace 'Topic' with the specific topic SET @start = CAST(@Year AS varchar(4)) + '-' + RIGHT('00' + CAST(@Month AS varchar(2)), 2) + '-01'; -- SET @start = '2023-11-01'; -- replace 'YYYY-MM' with the specific year and month SET @end = DATEADD(MONTH, 1, @start); WITH QuestionData AS ( SELECT p.Body AS QuestionBody, p.Id AS QuestionId, p.Title AS QuestionTitle, p.ViewCount AS ViewCount, p.AnswerCount AS AnswerCount, p.Score AS QuestionScore, CONCAT('https://dsp.stackexchange.com/q/', p.Id) AS QuestionURL FROM Posts p WHERE p.PostTypeId = 1 -- This denotes a Question AND p.CreationDate >= @start AND p.CreationDate < @end AND (LOWER(p.Body) LIKE '%'+@Topic+'%' OR LOWER(p.Title) LIKE '%'+@Topic+'%') ), AnswerData AS ( SELECT p.ParentId AS QuestionId, SUM(p.Score) AS TotalAnswerScore FROM Posts p WHERE p.PostTypeId = 2 -- This denotes an Answer GROUP BY p.ParentId ), QuestionComments AS ( SELECT c.PostId AS QuestionId, COUNT(*) AS QuestionCommentsCount FROM Comments c WHERE c.PostId IN (SELECT QuestionId FROM QuestionData) GROUP BY c.PostId ), AnswerComments AS ( SELECT p.ParentId AS QuestionId, COUNT(*) AS AnswerCommentsCount FROM Comments c JOIN Posts p ON p.Id = c.PostId WHERE p.ParentId IN (SELECT QuestionId FROM QuestionData) AND p.PostTypeId = 2 GROUP BY p.ParentId ) SELECT TOP 10 qd.QuestionId, qd.QuestionTitle, qd.QuestionURL, qd.ViewCount, qd.AnswerCount, ISNULL(qd.QuestionScore, 0) + ISNULL(ad.TotalAnswerScore, 0) AS TotalScore, ISNULL(qc.QuestionCommentsCount, 0) + ISNULL(ac.AnswerCommentsCount, 0) AS TotalCommentsCount FROM QuestionData qd LEFT JOIN AnswerData ad ON qd.QuestionId = ad.QuestionId LEFT JOIN QuestionComments qc ON qd.QuestionId = qc.QuestionId LEFT JOIN AnswerComments ac ON qd.QuestionId = ac.QuestionId ORDER BY qd.ViewCount DESC, qd.AnswerCount DESC, TotalScore DESC, TotalCommentsCount DESC