Up / Down Vote ratio on Posts, by month

1

Please login or register to vote for this query.

(click on this box to dismiss)

Ask Ubuntu

Q&A for Ubuntu users and developers

SELECT  gVt.zMonth                      AS [Month]
        , gVt.upVt * 1.0 / gVt.dwnVt    AS [Up/Down Vote ratio, on Posts (Q+A)]
FROM        (
    SELECT      zMonth  = DATEADD (month, DATEDIFF (month, 0, v.CreationDate), 0)
                , upVt  = SUM (CASE  WHEN v.VoteTypeId = 2  THEN 1  ELSE 0  END)
                , dwnVt = SUM (CASE  WHEN v.VoteTypeId = 3  THEN 1  ELSE 0  END)
    FROM        Votes v
    GROUP BY    DATEADD (month, DATEDIFF (month, 0, v.CreationDate), 0)
) gVt
WHERE       gVt.dwnVt != 0
ORDER BY    gVt.zMonth

Enter Parameters

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