Tags with most "valuable" accepted answers

0

Please login or register to vote for this query.

(click on this box to dismiss)

This query shows the number of accepted answers per tag and the average score of these answers.

Physical Fitness Meta

Q&A about the site for physical fitness professionals, athletes, trainers, and those providing health-related needs

-- Tags with most "valuable" accepted answers
-- This query shows the number of accepted answers per tag and the average score of these answers.

    SELECT
      Tags.TagName,
      (
        SELECT
          COUNT(*)
        FROM
          Posts
        JOIN
          PostTags ON Posts.Id = PostTags.PostId
        WHERE
          Posts.PostTypeId = 1 AND PostTags.TagId = Tags.Id
      ) AS Questions,
      (
        SELECT
          COUNT(*)
        FROM
          Posts
        JOIN
          PostTags ON Posts.Id = PostTags.PostId
        WHERE
          Posts.PostTypeId = 1 AND PostTags.TagId = Tags.Id AND Posts.AcceptedAnswerId > 0
      ) AS [With Accepted Answers],
      (
        SELECT
          ROUND(AVG(CAST(Posts.Score AS Float)), 1)
        FROM
          Posts
        JOIN
          Posts as p2 ON p2.AcceptedAnswerId = Posts.Id
        JOIN
          PostTags ON p2.Id = PostTags.PostId
        WHERE
          p2.PostTypeId = 1 AND PostTags.TagId = Tags.Id AND p2.AcceptedAnswerId > 0
      ) AS [Accepted Answer Score]
    FROM
      Tags
    WHERE
      (
        SELECT
          COUNT(Posts.Id)
        FROM
          Posts
        JOIN
          PostTags ON Posts.Id = PostTags.PostId
        WHERE
          Posts.PostTypeId = 1 AND PostTags.TagId = Tags.Id
      ) > 50
    ORDER BY
      [Accepted Answer Score] DESC

Enter Parameters

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