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.

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
          AVG(Posts.Score)
        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
      ) > 20

fork query

created jun 27 11 Wladimir.Palant