Plots total questions over time or total score over time, comparing up to 4 tags. Reference: http://meta.stackoverflow.com/q/260570/331508
Preguntas y respuestas sobre el sitio para programadores y profesionales de la informática
-- ShowScore: Enter 1 for scores, 0 for question counts -- Tag1: Tag name (lowercase) or 'NA' (uppercase) "Enter a tag name or uppercase NA or a single space" -- Tag2: Tag name (lowercase) or 'NA' (uppercase) "Enter a tag name or uppercase NA or a single space" -- Tag3: Tag name (lowercase) or 'NA' (uppercase) "Enter a tag name or uppercase NA or a single space" -- Tag4: Tag name (lowercase) or 'NA' (uppercase) "Enter a tag name or uppercase NA or a single space" DECLARE @ShowScore INT = ##ShowScore:int##; DECLARE @Tag1 varchar(255) = ##Tag1:string##; DECLARE @Tag2 varchar(255) = ##Tag2:string##; DECLARE @Tag3 varchar(255) = ##Tag3:string##; DECLARE @Tag4 varchar(255) = ##Tag4:string##; WITH QuestStatsByMonth AS ( SELECT DATEADD (month, DATEDIFF (month, 0, q.CreationDate), 0) AS [Month], t.TagName, SUM (q.Score) AS TotalScore, COUNT (q.Id) AS NumQuests FROM Posts q INNER JOIN PostTags pt ON q.Id = pt.PostId INNER JOIN Tags t ON t.Id = pt.TagId WHERE q.PostTypeId = 1 AND t.TagName IN (@Tag1, @Tag2, @Tag3, @Tag4) GROUP BY DATEADD (month, DATEDIFF (month, 0, q.CreationDate), 0), t.TagName ) SELECT q.[Month], CASE @ShowScore WHEN 1 THEN q.TagName + ' score' ELSE q.TagName + ' questions' END AS [Tag], CASE @ShowScore WHEN 1 THEN SUM (h.TotalScore) ELSE SUM (h.NumQuests) END AS Cumulative FROM QuestStatsByMonth q LEFT JOIN QuestStatsByMonth h ON h.[Month] <= q.[Month] AND h.TagName = q.TagName GROUP BY q.[Month], q.TagName ORDER BY q.[Month], q.TagName