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