Average scores of posts of the site after 30 days

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.

Bicycles Meta

Q&A about the site for people who build and repair bicycles, people who train cycling, or commute on bicycles

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

Enter Parameters

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