Question Count or Score growth over time by tag comparison


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:

Role-playing Games

Q&A for gamemasters and players of tabletop, paper-and-pencil role-playing games

-- 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"
DECLARE @ShowScore INT      = ##ShowScore:int##;
DECLARE @Tag1 varchar(255)  = ##Tag1:string##;
DECLARE @Tag2 varchar(255)  = ##Tag2:string##;

WITH QuestStatsByMonth  AS (
    SELECT      DATEADD (month, DATEDIFF (month, 0, q.CreationDate), 0)  AS [Month],
    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)

    GROUP BY    DATEADD (month, DATEDIFF (month, 0, q.CreationDate), 0),
    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

Enter Parameters

Switch sites:
loading Hold tight while we fetch your results