1

Please login or register to vote for this query.

(click on this box to dismiss)

Shows Views, Votes, and Answers per Question for the top N tags

Q&A for pro webmasters

-- Tag Stats per Question
-- Shows Views, Votes, and Answers per Question for the top N tags

DECLARE @minQuestionCount int = 500
DECLARE @topN int = 50

DECLARE @topTagQuestions table
(
  TagId int NOT NULL,
  PostId int NOT NULL,
  PRIMARY KEY CLUSTERED(TagId, PostId)
)

INSERT INTO @topTagQuestions (TagId, PostId)
  SELECT
    TagId,
    PostId
    FROM PostTags
    WHERE
      TagId IN
      (
        SELECT TOP (@topN)
          Id
          FROM Tags
          WHERE Count > @minQuestionCount
          ORDER BY Count DESC
      )

SELECT
  t.TagName,
  TotalQuestions,
  TotalViews,
  TotalVotes,
  TotalAnswers,
  CAST((TotalViews / (1.0 * TotalQuestions)) AS decimal(18, 2)) AS ViewsPerQuestion,
  CAST((TotalVotes / (1.0 * TotalQuestions)) AS decimal(18, 2)) AS VoteActivityPerQuestion,
  CAST((TotalAnswers / (1.0 * TotalQuestions)) AS decimal(18, 2)) AS AnswersPerQuestion
  FROM
  (
    SELECT
      tq.TagId,
      SUM(1) AS TotalQuestions,
      SUM(q.ViewCount) AS TotalViews,
      SUM(q.AnswerCount) AS TotalAnswers
      FROM @topTagQuestions tq
      INNER JOIN Posts q ON q.Id = tq.PostId
      GROUP BY tq.TagId
  ) a
  INNER JOIN
  (
    SELECT
      tq.TagId,
      SUM(1) AS TotalVotes
      FROM @topTagQuestions tq
      INNER JOIN Posts a ON a.ParentId = tq.PostId
      INNER JOIN Votes pv ON pv.PostId = a.Id
      WHERE pv.VoteTypeId IN (1, 2, 3)
      GROUP BY tq.TagId
  ) b ON b.TagId = a.TagId
  INNER JOIN Tags t ON t.Id = a.TagId
  ORDER BY ViewsPerQuestion DESC

fork query

created jun 2 11 Jon Seigel