There is a little error, but only for the last month.
Q&A for apps, scripts, and development with the Stack Exchange API
DECLARE @Until DATETIME; SELECT @Until = DATEFROMPARTS( YEAR(MAX(CreationDate)), MONTH(MAX(CreationDate)), 1 ) FROM Votes; CREATE TABLE #Raw( Month DATETIME NOT NULL, PostTypeId INT NOT NULL, /* Here we use VoteTypeId = 0 to store the count of the posts of that month. It is required because we need to parallelize the query below, in order to run even on the SO. */ VoteTypeId INT NOT NULL, Count INT NOT NULL, PRIMARY KEY(Month, PostTypeId, VoteTypeId) ); INSERT INTO #Raw SELECT DATEFROMPARTS( YEAR(Votes.CreationDate), MONTH(Votes.CreationDate), 1) AS Month, PostsWithDeleted.PostTypeId, Votes.VoteTypeId, COUNT(*) AS Count FROM PostsWithDeleted, Votes WHERE Votes.PostId = PostsWithDeleted.Id AND Votes.VoteTypeId BETWEEN 2 AND 3 AND PostsWithDeleted.PostTypeId BETWEEN 1 AND 2 AND PostsWithDeleted.CreationDate < @Until AND DATEDIFF(d, PostsWithDeleted.CreationDate, Votes.CreationDate) BETWEEN 0 AND 30 GROUP BY DATEFROMPARTS( YEAR(Votes.CreationDate), MONTH(Votes.CreationDate), 1), Votes.VoteTypeId, PostsWithDeleted.PostTypeId UNION ALL SELECT DATEFROMPARTS( YEAR(CreationDate), MONTH(CreationDate), 1) AS Month, PostTypeId, 0 AS VoteTypeId, COUNT(*) AS Count FROM PostsWithDeleted WHERE PostTypeId <= 2 AND CreationDate < @Until GROUP BY DATEFROMPARTS( YEAR(CreationDate), MONTH(CreationDate), 1), PostTypeId; SELECT Ups.Month, 'Average score of ' + PostTypes.Name + 's' AS Type, 1.0 * (Ups.Count - Downs.Count) / PostsMonth.Count FROM #Raw Ups, #Raw Downs, #Raw PostsMonth, PostTypes WHERE Ups.Month = Downs.Month AND Ups.Month = PostsMonth.Month AND Ups.PostTypeId = Downs.PostTypeId AND Ups.PostTypeId = PostsMonth.PostTypeId AND Ups.VoteTypeId = 2 AND Downs.VoteTypeId = 3 AND PostsMonth.VoteTypeId = 0 AND Ups.PostTypeId = PostTypes.Id ORDER BY Ups.Month DESC, Ups.PostTypeId