Percent of questions below score threshhold per month

0

Please login or register to vote for this query.

(click on this box to dismiss)

Game Development

Q&A for professional and independent game developers

-- MinimumScore: Enter the minimum score threshhold
DECLARE @MinimumScore INT      = ##MinimumScore:int##;

WITH QuestionStatsByMonth  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)
),
 DownvoteQuestionStatsByMonth  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 AND q.Score <= @MinimumScore

    GROUP BY    DATEADD (month, DATEDIFF (month, 0, q.CreationDate), 0)
)

SELECT
    q.[Month], 'percent <= ##MinimumScore##' AS [Tag],

    (100 * SUM(h.NumQuests)) / SUM(q.NumQuests) AS MonthlyValue

FROM        QuestionStatsByMonth   q
LEFT JOIN   DownvoteQuestionStatsByMonth   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