Q&A about the site for user experience researchers and experts
DECLARE @start datetime, @end datetime; DECLARE @Year int, @Month int; DECLARE @isSetTopic nvarchar(10); DECLARE @Topic nvarchar(max); DECLARE @SiteUrl VARCHAR(MAX) SET @SiteUrl = ( SELECT 'https://' + CASE WHEN DB_NAME() = 'StackOverflow' THEN 'stackoverflow' WHEN DB_NAME() = 'ServerFault' THEN 'serverfault' WHEN DB_NAME() = 'SuperUser' THEN 'superuser' WHEN DB_NAME() LIKE 'StackExchange.%%' THEN REPLACE(DB_NAME(), 'StackExchange.', '') ELSE 'stackexchange' -- Default or if it doesn't match anything specific END + '.stackexchange.com/q/' ) SET @Year = ##Year:int##; -- replace 'Year' with the specific year SET @Month = ##Month:int##; -- replace 'Month' with the specific month SET @isSetTopic = ##isSetTopic:string##; -- replace 'isSetTopic' with the specific isSetTopic 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(LOWER(@SiteUrl), p.Id) AS QuestionURL FROM Posts p WHERE p.PostTypeId = 1 -- This denotes a Question AND p.CreationDate >= @start AND p.CreationDate < @end AND ( CASE WHEN @isSetTopic = 'false' OR @Topic='' THEN 1 ELSE CASE WHEN @isSetTopic = 'true' and (LOWER(p.Body) LIKE '%' + @Topic + '%' OR LOWER(p.Title) LIKE '%' + @Topic + '%') THEN 1 ELSE 0 END END = 1 ) ), 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