average score by tag

0

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
     WHERE p.PostTypeId = 1 OR p.PostTypeId = 2
)
select PrimaryTag
  , PostType
  , Count (PostId)
  , Avg(PostScore) as AvgScore
  , (SELECT Top 1 percentile_disc(0.5) 
        WITHIN GROUP (ORDER BY PostScore) 
        OVER (PARTITION BY [PrimaryTag], PostType) 
        FROM PostData hack 
        WHERE hack.PrimaryTag = PrimaryTag And hack.PostType = PostType
    ) as MedianScore
  , stdev(PostScore) as StdDevScore
  
from PostData
GROUP BY PrimaryTag, PostType
order by Count(PostId) asc

Enter Parameters

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