0

Please login or register to vote for this query.

(click on this box to dismiss)

Plots total questions over time or total score over time, comparing up to 4 tags. Reference: http://meta.stackoverflow.com/q/260570/331508

Q&A for professional and enthusiast programmers

DECLARE @ShowScore INT      = 0;
DECLARE @Tag1 varchar(255)  = 'angularjs';
DECLARE @Tag2 varchar(255)  = 'ember.js';
DECLARE @Tag3 varchar(255)  = 'knockout.js';
DECLARE @Tag4 varchar(255)  = 'meteor';
DECLARE @Tag5 varchar(255)  = 'mercury';
DECLARE @Tag6 varchar(255)  = 'backbone.js';
DECLARE @Tag7 varchar(255)  = 'polymer';
DECLARE @Tag8 varchar(255)  = 'reactjs';
DECLARE @Tag9 varchar(255)  = 'angular2';

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, @Tag5, @Tag6, @Tag7, @Tag8, @Tag9)

    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

fork query

edited feb 3 17 Thom.Kiesewetter

created jun 15 14 Brock Adams