average score by tag


Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

WITH PostData As (
  SELECT p.Id As PostId
    , (SELECT TOP 1 tag.tagname 
      FROM PostTags as pt 
        INNER JOIN Tags as tag ON pt.tagId = tag.Id 
        WHERE pt.postId = p.Id OR pt.PostId = p.ParentId
        ORDER BY (SELECT Count(pt_inner.PostId) 
          FROM PostTags as pt_inner 
          WHERE pt_inner.TagId = pt.TagId)
   ) As [PrimaryTag]
   , ptypes.Name as PostType
   , p.Score as PostScore
   FROM Posts p 
     INNER JOIN PostTypes ptypes ON ptypes.Id = p.PostTypeId
select PrimaryTag
  , PostType
  , Count (PostId)
  --, avg(PostScore) as AvgScore
  --, percentile_disc(0.5) WITHIN GROUP (ORDER BY PostScore) OVER (PARTITION BY [PrimaryTag], PostType) 
  --  as MedianScore
  --, stdev(PostScore) OVER (PARTITION BY [PrimaryTag], PostType) as StdDevScore
from PostData
GROUP BY PrimaryTag, PostType
order by Count(PostId) DESC

Enter Parameters

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