Views per vote

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

WITH VotesByMonth  AS (
    SELECT DATEADD (month, DATEDIFF (month, 0, v.CreationDate), 0)  AS [Month],
    COUNT (v.Id) AS Votes

    FROM Votes v 
    JOIN Posts p ON (p.Id = v.PostId) AND p.PostTypeId = 1
    WHERE v.VoteTypeId=2 or v.VoteTypeId=1 and DATEDIFF(day, p.CreationDate, v.CreationDate) < 31

    GROUP BY DATEADD (month, DATEDIFF (month, 0, v.CreationDate), 0)
),
ViewsByMonth  AS (
    SELECT DATEADD (month, DATEDIFF (month, 0, p.CreationDate), 0) AS [Month],
    SUM (p.ViewCount) AS Views

    FROM Posts p WHERE p.PostTypeId = 1

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

SELECT
    v.[Month], 
    dv.Views/v.Votes AS 'ViewsPerVote'

FROM VotesByMonth v
JOIN ViewsByMonth dv ON v.[Month] = dv.[Month]
WHERE dv.Views/v.Votes<10000
ORDER BY v.[Month]

Enter Parameters

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