Median votes per post

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for programmers

with AllVotes as (
select datepart(year, p.CreationDate) as yr
     , datepart(q, p.CreationDate) as q 
     , cast(count(v.Id) as float) 
     / cast(count(distinct p.Id) as float) vpp
  from Posts p
  left outer join Votes v
    on p.Id = v.PostId
 where p.PostTypeId in (1, 2)
 group by datepart(year, p.CreationDate)
        , datepart(q, p.CreationDate)
        )
 , FirstStep as ( 
 select vpp
      , cast(yr as varchar(4)) as yr
      , cast(q as varchar(1)) as q
      , row_number() over ( partition by yr, q order by vpp asc ) as RowAsc
      , row_number() over ( partition by yr, q order by vpp desc ) as RowDesc
   from AllVotes )
select yr + '.' + q, avg(vpp) as [Median votes per post]
  from FirstStep
 where RowAsc in (RowDesc, RowDesc - 1, RowDesc + 1)
 group by yr + '.' +  q
 order by yr + '.' +  q

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results
:records returned in :time ms:cached