Casted votes / posted posts, grouped by month & posttype & votetype

1

Please login or register to vote for this query.

(click on this box to dismiss)

Physics

Q&A for active researchers, academics and students of physics

CREATE TABLE #VoteStat (
  Month DATETIME,
  PostTypeId INT,
  VoteTypeId INT,
  Count INT
);

CREATE TABLE #PostStat (
  Month DATETIME,
  PostTypeId INT,
  Count INT
);

INSERT INTO #VoteStat
SELECT
  DATEFROMPARTS(
    DATEPART(yyyy, Votes.CreationDate),
    DATEPART(mm, Votes.CreationDate),
    1) AS Month,
  PostsWithDeleted.PostTypeId,
  Votes.VoteTypeId,
  COUNT(*) AS Count
FROM PostsWithDeleted, Votes
WHERE
  PostsWithDeleted.Id = Votes.PostId
  AND
  PostsWithDeleted.PostTypeId BETWEEN 1 AND 2
  AND
  Votes.VoteTypeId BETWEEN 2 AND 3
GROUP BY
  DATEFROMPARTS(
    DATEPART(yyyy, Votes.CreationDate),
    DATEPART(mm, Votes.CreationDate),
    1),
  PostsWithDeleted.PostTypeId,
  Votes.VoteTypeId;

INSERT INTO #PostStat
SELECT
  DATEFROMPARTS(
    DATEPART(yyyy, PostsWithDeleted.CreationDate),
    DATEPART(mm, PostsWithDeleted.CreationDate),
    1) AS Month,
  PostsWithDeleted.PostTypeId,
  COUNT(*) AS Count
FROM PostsWithDeleted
WHERE
  PostsWithDeleted.PostTypeId BETWEEN 1 AND 2
GROUP BY
  DATEFROMPARTS(
    DATEPART(yyyy, PostsWithDeleted.CreationDate),
    DATEPART(mm, PostsWithDeleted.CreationDate),
    1),
  PostsWithDeleted.PostTypeId;

SELECT
  PostStat.Month,
  PostTypes.Name + ' ' + VoteTypes.Name AS VoteType,
  1.0 * VoteStat.Count / PostStat.Count AS Ratio
FROM #PostStat PostStat, #VoteStat VoteStat, PostTypes, VoteTypes
WHERE
  PostStat.Month = VoteStat.Month
  AND
  PostStat.PostTypeId = VoteStat.PostTypeId
  AND
  PostTypes.Id = PostStat.PostTypeId
  AND
  VoteTypes.Id = VoteStat.VoteTypeId
ORDER BY
  PostStat.Month DESC,
  PostStat.PostTypeId,
  VoteStat.VoteTypeId;

Enter Parameters

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