Totals by Tag for Open Questions

0

Please login or register to vote for this query.

(click on this box to dismiss)

Shows Total Questions, Views and Answers for the top N tags

Arqade

Q&A for passionate videogamers on all platforms

-- Totals by Tag for Open Questions
-- Shows Total Questions, Views and Answers for the top N tags

DECLARE @minQuestionCount int = 1

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

INSERT INTO @topTagQuestions (TagId, PostId)
  SELECT
    TagId,
    PostId
    FROM PostTags
    WHERE
      TagId IN
      (
        SELECT
          Id
          FROM Tags
          WHERE Count > @minQuestionCount
      )

SELECT
  t.TagName,
  TotalQuestions,
  TotalViews,
  TotalAnswers
  FROM
  (
    SELECT
      tq.TagId,
      SUM(1) AS TotalQuestions,
      SUM(q.ViewCount) AS TotalViews,
      SUM(q.AnswerCount) AS TotalAnswers,
      SUM(case when q.AnswerCount = 0 then 0 else 1 end) AS TotalAnswered,
      SUM(case when q.AcceptedAnswerId is null then 0 else 1 end) AS TotalAccepted
      FROM @topTagQuestions tq
      INNER JOIN Posts q ON q.Id = tq.PostId
      WHERE q.ClosedDate is null
      GROUP BY tq.TagId
  ) a
  INNER JOIN Tags t ON t.Id = a.TagId
  ORDER BY TotalQuestions DESC, TotalViews DESC, TotalAnswers DESC

Enter Parameters

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