Questions, answers and votes per day

3

Please login or register to vote for this query.

(click on this box to dismiss)

スタック・オーバーフロー

プログラマーとプログラミングに熱心な人々のための質問と回答

WITH postcount AS (
  SELECT 
    dateadd(day, datediff(day, 0, CreationDate), 0) AS datetime,
    COUNT(*) AS total
  FROM Posts
  WHERE PostTypeId IN (1,2)
  GROUP BY dateadd(day, datediff(day, 0, CreationDate), 0)
),
qcount AS (
  SELECT 
    dateadd(day, datediff(day, 0, CreationDate), 0) AS datetime,
    COUNT(*) AS total
  FROM Posts
  WHERE PostTypeId = 1
  GROUP BY dateadd(day, datediff(day, 0, CreationDate), 0)
),
acount AS (
  SELECT 
    dateadd(day, datediff(day, 0, CreationDate), 0) AS datetime,
    COUNT(*) AS total
  FROM Posts
  WHERE PostTypeId = 2
  GROUP BY dateadd(day, datediff(day, 0, CreationDate), 0)
),
usercount AS (
  SELECT 
    dateadd(day, datediff(day, 0, CreationDate), 0) AS datetime,
    COUNT(*) AS total
  FROM Users
  GROUP BY dateadd(day, datediff(day, 0, CreationDate), 0)
),
votecount AS (
  SELECT 
    dateadd(day, datediff(day, 0, CreationDate), 0) AS datetime,
    COUNT(*) AS total
  FROM Votes
  WHERE VoteTypeId IN (1,2,3)
  GROUP BY dateadd(day, datediff(day, 0, CreationDate), 0)
),
stats AS (SELECT 
  p.datetime AS 'date',
  v.total AS 'votes per day',
  q.total AS 'questions per day',
  a.total AS 'answers per day',
  p.total AS 'posts per day'
FROM
  postcount p 
  FULL OUTER JOIN usercount u on u.datetime = p.datetime 
  FULL OUTER JOIN votecount v on v.datetime = p.datetime
  FULL OUTER JOIN qcount q on q.datetime = p.datetime
  FULL OUTER JOIN acount a on a.datetime = p.datetime
  )
  
SELECT
  date,
  [votes per day],
  [questions per day],
  [answers per day]
FROM stats
WHERE
  stats.date >= ##StartDate:string?2014-12-16##
ORDER BY stats.date

Enter Parameters

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