Answers per month and average answer score per month


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      DATEADD (month, DATEDIFF (month, 0, q.CreationDate), 0)  AS [Month],
    SUM (q.Score)               AS TotalScore,
    COUNT (q.Id)                AS NumQuests

    FROM        Posts           q
    WHERE       q.PostTypeId    = 2

    GROUP BY    DATEADD (month, DATEDIFF (month, 0, q.CreationDate), 0)
    CASE    @ShowScore
        WHEN    1 THEN  'average score'
        ELSE            'questions'
    END AS [Tag],

    CASE    @ShowScore
        WHEN    1 THEN  SUM (h.TotalScore) / SUM (h.NumQuests)
        ELSE            SUM (h.NumQuests)
    END AS Cumulative

FROM        QuestStatsByMonth   q
LEFT JOIN   QuestStatsByMonth   h
ON          h.[Month]           = q.[Month]
GROUP BY    q.[Month]
ORDER BY    q.[Month]

Enter Parameters

Switch to meta site
loading Hold tight while we fetch your results