Questions per month and average score


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
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    = 1

    GROUP BY    DATEADD (month, DATEDIFF (month, 0, q.CreationDate), 0)
    SUM (h.TotalScore) / SUM (h.NumQuests) as [Average score],
    SUM (h.NumQuests) as [Number of posts]

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