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:

Korean Language

Q&A for linguists, teachers and students of the Korean language

-- ShowScore: Enter 1 for scores, 0 for question counts
-- Tag1: Tag name (lowercase) or 'NA' (uppercase)
-- Tag2: Tag name (lowercase) or 'NA' (uppercase)
-- Tag3: Tag name (lowercase) or 'NA' (uppercase)
DECLARE @Tag1 varchar(255)  = ##Tag1:string##;
DECLARE @Tag2 varchar(255)  = ##Tag2:string##;
DECLARE @Tag3 varchar(255)  = ##Tag3: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        PostsWithDeleted  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)

    GROUP BY    DATEADD (month, DATEDIFF (month, 0, q.CreationDate), 0),
    q.[Month], q.TagName, SUM (h.NumQuests)

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 to meta site
loading Hold tight while we fetch your results