Questions per month and average score

0

Please login or register to vote for this query.

(click on this box to dismiss)

Audio-Video Production Meta

Q&A for engineers, producers, editors, and enthusiasts spanning the fields of audio, video, and media creation

-- 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)
)
SELECT
    q.[Month],
    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

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