0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professional and enthusiast programmers

WITH PostData As (
  SELECT p.Id As PostId
    , t.tagname As Tag
   , ptypes.Name As PostType
   , p.Score As PostScore
   , Len(p.Body) As PostLength
   FROM Posts p 
     INNER JOIN PostTypes ptypes ON ptypes.Id = p.PostTypeId
     RIGHT OUTER JOIN PostTags AS pt ON (pt.PostId = p.Id OR pt.PostId = p.ParentId)
     INNER JOIN Tags as t ON pt.TagId = t.Id
     WHERE p.PostTypeId = 1 OR p.PostTypeId = 2
)
select out.Tag
  , out.PostType
  , Count (out.PostId) As PostCount
  , Avg (out.PostScore) as AvgScore
  --, (SELECT Top 1 percentile_disc(0.5) 
  --      WITHIN GROUP (ORDER BY hack.PostScore) 
  --      OVER (PARTITION BY hack.PrimaryTag, hack.PostType) 
  --      FROM PostData hack 
  --      WHERE hack.PrimaryTag = out.PrimaryTag And hack.PostType = out.PostType
  --  ) as MedianScore
  , StDev (out.PostScore) as StdDevScore
  , Avg (out.PostLength) as AvgLength
  , StDev (out.PostLength) as StDevLength
  
from PostData out
GROUP BY out.Tag, out.PostType
order by Count(out.PostId) DESC

fork query

edited apr 17 18

created apr 13 18 Vogel612