Question Body length percentile and statistics

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
      id,
      ntile(100) over (order by len(body)) percentile
      FROM posts where posttypeid = 1),
Answer as 
(SELECT 
      a.Parentid,
      a.creationDate,
      row_number() over (partition by a.ParentID order by a.id) rn
 FROM
      cte 
      INNER JOIN posts a
      ON cte.id = a.Parentid) 
      

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,
    AVG(DATEDIFF ( mi , p.creationDate , a.CreationDate )) avgFirstAnswerInMinutes
FROM
   posts p
   INNER join cte 
   on p.id = cte.id
   LEFT JOIN Answer a
   ON cte.id = a.Parentid
WHERE a.rn = 1
group by cte.percentile
order by cte.percentile

Enter Parameters

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