0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professional and enthusiast programmers

DECLARE @t DATETIME = GETDATE(); -- today
DECLARE @f CHAR(5) = '#,###';    -- format string

/* TODO: add metrics for higest scoring answer and upvotes/months */

SELECT TOP ##Limit:int?100##
  q.Id AS [Post Link],
  -- FORMAT(q.CreationDate, 'yyyy-MM-dd', 'en-US') AS [CreationDate],
  FORMAT(q.CreationDate, 'yyyy-MM', 'en-US') AS [CreationDate],
  -- DATEDIFF(month, q.CreationDate, @t) AS [Months],
  FORMAT(q.Score, @f) AS [Score],
  -- This is just an approximation, upvotes will give more accurate indicator
  q.Score / DATEDIFF(month, q.CreationDate, @t) AS [Score/Month],
  FORMAT(ViewCount, @f) AS [Views],
  FORMAT(ViewCount / DATEDIFF(month, q.CreationDate, @t), @f) AS [Views/Month],
  -- FavoriteCount,
  AnswerCount
FROM Posts q
  INNER JOIN PostTags pt ON q.Id = pt.PostId
  INNER JOIN Tags t ON pt.TagId = t.Id
WHERE q.PostTypeId = 1 -- questions
  AND t.TagName = ##Tag:string?asp##
  AND q.ClosedDate IS NULL
ORDER BY ViewCount / DATEDIFF(month, q.CreationDate, @t) DESC

fork query

edited aug 2 14 monodaxmobile

created aug 2 14 Cupcake