Tag set A versus Tag set B, Growth comparison

1

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, for questions with tags X, versus questions with tags Y. Reference: https://stackapps.com/q/7696/7653

Stack Overflow

Q&A for professional and enthusiast programmers

-- ShowScore: Enter 1 for scores, 0 for question counts
-- LeftTag1: Left Tag name 1 (lowercase and REQUIRED) "Enter a tag name or uppercase NA."
-- LeftTag2: Left Tag name 2 (lowercase) or 'NA'      "Enter a tag name or uppercase NA."
-- RightTag1: Right Tag name 1 (lowercase and REQUIRED) "Enter a tag name or uppercase NA."
-- RightTag2: Right Tag name 2 (lowercase) or 'NA'      "Enter a tag name or uppercase NA."
DECLARE @ShowScore INT           = ##ShowScore:int##;
DECLARE @LeftTag1 varchar(255)   = ##LeftTag1:string##;
DECLARE @LeftTag2 varchar(255)   = ##LeftTag2:string##;
DECLARE @RightTag1 varchar(255)  = ##RightTag1:string##;
DECLARE @RightTag2 varchar(255)  = ##RightTag2:string##;

-- Pre select common stuff...
DECLARE @LeftTagSetName varchar(255)  = (SELECT @LeftTag1  + CASE @LeftTag2  WHEN 'NA' THEN '' ELSE ' and ' + @LeftTag2 END);
DECLARE @RightTagSetName varchar(255) = (SELECT @RightTag1 + CASE @RightTag2 WHEN 'NA' THEN '' ELSE ' and ' + @RightTag2 END);

DECLARE @AxisSuffix varchar(255) = (SELECT CASE @ShowScore WHEN 1 THEN ' score' ELSE ' questions' END);

DECLARE @LeftTag1ID INT     = (SELECT Id FROM Tags WHERE TagName = @LeftTag1)
DECLARE @RightTag1ID INT    = (SELECT Id FROM Tags WHERE TagName = @RightTag1)

DECLARE @LeftTag2ID INT     = (
    CASE @LeftTag2 WHEN 'NA' THEN @LeftTag1ID ELSE (SELECT Id FROM Tags WHERE TagName = @LeftTag2) END
);
DECLARE @RightTag2ID INT    = (
    CASE @RightTag2 WHEN 'NA' THEN @RightTag1ID ELSE (SELECT Id FROM Tags WHERE TagName = @RightTag2) END
);

WITH
    LeftQuestStatsByMonth  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

        WHERE       q.PostTypeId    = 1
        AND         pt.TagId        = @LeftTag1ID
        AND         EXISTS (SELECT * FROM PostTags WHERE q.Id = PostId AND TagId = @LeftTag2ID)

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

        WHERE       q.PostTypeId    = 1
        AND         pt.TagId        = @RightTag1ID
        AND         EXISTS (SELECT * FROM PostTags WHERE q.Id = PostId AND TagId = @RightTag2ID)

        GROUP BY    DATEADD (month, DATEDIFF (month, 0, q.CreationDate), 0)
    ),
    MergedStats AS (
        SELECT
            [Month],
            @LeftTagSetName + @AxisSuffix  AS axisLable,
            CASE    @ShowScore
                WHEN    1 THEN  TotalScore
                ELSE            NumQuests
            END AS scoreOrCount

        FROM        LeftQuestStatsByMonth

        UNION ALL SELECT
            [Month],
            @RightTagSetName + @AxisSuffix  AS axisLable,
            CASE    @ShowScore
                WHEN    1 THEN  TotalScore
                ELSE            NumQuests
            END AS scoreOrCount

        FROM        RightQuestStatsByMonth
    )
SELECT
    q.[Month],
    q.axisLable,
    SUM (h.scoreOrCount)    AS Cumulative

FROM        MergedStats     q
LEFT JOIN   MergedStats     h
ON          h.[Month]       <= q.[Month]
AND         h.axisLable     = q.axisLable
GROUP BY    q.[Month], q.axisLable

--ORDER BY    [Month], axisLable

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results