C#, Java and Android -- Growth over time comparison

1

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

-- ShowScore: Enter 1 for scores, 0 for question counts
DECLARE @ShowScore INT      = ##ShowScore:int##;

WITH QuestStatsByMonth  AS (
    SELECT
        c.[Month],
        c.Tagname,
        SUM (c.Score)   AS TotalScore,
        COUNT (c.Id)    AS NumQuests
    FROM (
        SELECT      DATEADD (month, DATEDIFF (month, 0, q.CreationDate), 0)  AS [Month],
            t.TagName,
            q.Score,
            q.Id

        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 ('c#', 'java', 'android')

        UNION ALL SELECT
            DATEADD (month, DATEDIFF (month, 0, q.CreationDate), 0)  AS [Month],
            'java without android'  AS TagName,
            q.Score,
            q.Id

        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       = 'java'
        AND         NOT EXISTS (
                        SELECT      pt2.PostId
                        FROM        PostTags        pt2
                        INNER JOIN  Tags            t2
                        ON          t2.Id           = pt2.TagId
                        WHERE       pt2.PostId      = q.Id
                        AND         t2.TagName      = 'android'
                    )
    ) AS c

    GROUP BY c.[Month], c.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

Enter Parameters

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