instruction: Votes per post by month.
response: WITH posts_by_month AS (SELECT CAST(DATEADD(month, DATEDIFF(month, GETDATE(), CreationDate), GETDATE()) AS DATE) AS Month, COUNT(*) AS post_count FROM Posts AS p GROUP BY DATEDIFF(month, GETDATE(), CreationDate)), votes_by_month AS (SELECT CAST(DATEADD(month, DATEDIFF(month, GETDATE(), CreationDate), GETDATE()) AS DATE) AS Month, vt.Name, COUNT(*) AS vote_count FROM Votes AS v JOIN VoteTypes AS vt ON vt.Id = VoteTypeId WHERE VoteTypeId IN (1, 2, 3) GROUP BY DATEDIFF(month, GETDATE(), CreationDate), vt.Name) SELECT v.Month, CAST(vote_count AS FLOAT) / post_count AS Name FROM posts_by_month AS p JOIN votes_by_month AS v ON p.Month = v.Month ORDER BY v.Month