1

Please login or register to vote for this query.

(click on this box to dismiss)

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

fork query

edited may 7

created may 7 peterh - PLEASE DELETE THIS ACCOUNT