AngularJS average metrics by year

0

Please login or register to vote for this query.

(click on this box to dismiss)

Esperanto Language

Q&A for teachers and students of the Esperanto language

DECLARE @tagName NVARCHAR(255) = 'angularjs'
-- TagName: Tag name of the question.
DECLARE @beginDate DATETIME = '2016-01-01'
DECLARE @endDate DATETIME = '2020-10-28'

SELECT
  DATEPART(YEAR, byMonth.Month) as year, 
  avg(ViewCount) as ViewCountMean,
  avg(AnswerCount) as AnswerCountMean, 
  avg(CommentCount) as CommentCountMean,
  avg(FavoriteCount) as FavoriteCountMean,
  avg(Score) as ScoreMean
FROM
(SELECT T.Month,
  sum(ViewCount) as ViewCount,
  sum(AnswerCount) as AnswerCount, 
  sum(CommentCount) as CommentCount,
  sum(FavoriteCount) as FavoriteCount,
  sum(Score) as Score
FROM
(SELECT
  CAST(
    CAST(DATEPART(YEAR, CreationDate) AS NVARCHAR) + '-' + 
    CAST(DATEPART(MONTH, CreationDate) AS NVARCHAR) + '-01'
      AS DATETIME) AS 'Month',
      p.ViewCount, p.AnswerCount, p.CommentCount, p.FavoriteCount, p.Score
FROM Posts p
  INNER JOIN PostTags pt ON p.Id = pt.PostId
  INNER JOIN Tags t ON t.Id = pt.TagId
  WHERE (p.PostTypeId = 1) AND (t.TagName = @tagName)
  AND (p.CreationDate BETWEEN @beginDate AND @endDate)
GROUP BY 
  DATEPART(YEAR, CreationDate), 
  DATEPART(MONTH, CreationDate),
  p.ViewCount, p.AnswerCount, p.CommentCount, p.FavoriteCount, p.Score) AS T
GROUP BY T.Month) AS byMonth
GROUP BY  DATEPART(YEAR, byMonth.Month)
ORDER BY DATEPART(YEAR, byMonth.Month) ASC;

Enter Parameters

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