Votes over time and posts over time

1

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

SELECT
    52 * DATEPART(year, v.CreationDate) + DATEPART(week, v.CreationDate) AS [WeekNum],
    COUNT(v.Id) AS [Count]
  INTO #votes
  FROM Votes AS v
  WHERE
    v.VoteTypeId IN (2, 3) -- UpMod, DownMod
    AND 52 * DATEPART(year, v.CreationDate) + DATEPART(week, v.CreationDate) <> 52 * DATEPART(year, GETDATE()) + DATEPART(week, GETDATE())
  GROUP BY 52 * DATEPART(year, v.CreationDate) + DATEPART(week, v.CreationDate)
    
SELECT
    52 * DATEPART(year, p.CreationDate) + DATEPART(week, p.CreationDate) AS [WeekNum],
    COUNT(p.Id) AS [Count]
  INTO #posts
  FROM Posts AS p
  WHERE 
    p.PostTypeId IN (1, 2) -- Question, Answer 
    AND 52 * DATEPART(year, p.CreationDate) + DATEPART(week, p.CreationDate) <> 52 * DATEPART(year, GETDATE()) + DATEPART(week, GETDATE())
  GROUP BY 52 * DATEPART(year, p.CreationDate) + DATEPART(week, p.CreationDate)


SELECT
    p.WeekNum,
    v.Count AS [Votes],
    p.Count AS [Posts]
  FROM
    #posts p
    FULL OUTER JOIN #votes v
      ON p.WeekNum = v.WeekNum
  ORDER BY
    p.WeekNum ASC

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results