Question Body length percentile and statistics with tag input

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 cte as (
SELECT
      p.id,
      ntile(100) over (order by len(p.body)) percentile
      FROM posts p
      INNER JOIN posttags pt
      ON p.id = pt.postId
      INNER JOIN tags t
      ON pt.tagid = t.id
       where p.posttypeid = 1
       and t.tagname = ##tagname:string##
      )

SELECT

    cte.percentile,
    min(len(p.body)) LengthStart,
    max(len(p.body)) LengthStop,
    avg(cast(p.score as decimal(10,4))) avgScore,
    avg(cast(p.answerCount as decimal(10,4))) AvgAnswerCount,
    avg(cast(p.viewCount as decimal(10,4))) AvgViewCount,
    avg(cast(p.CommentCount as decimal(10,4))) AvgCommentCount
FROM
   posts p
   inner join cte 
   on p.id = cte.id

group by cte.percentile
order by cte.percentile

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results