0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professionals and students of engineering

-- compute for each question how many votes it has 
--(question means posttypeid = 1)
with a as (
   Select p.Id, 
          Count(v.PostId) n_votes
   from Votes v join PostsWithDeleted p 
   on v.PostId = p.Id
   where p.PostTypeId in (1) 
     and v.VoteTypeId in (2,3)
   group by p.Id
),

--- add posts with zero counts which are not being counted by the scan on the votes table
b as (
   select pp.Id, 
          Year(pp.CreationDate) pyear,
          Case 
             When n_votes > 0 then n_votes
             else 0 
          end  n_votes
   from a full outer join PostsWithDeleted pp 
   on a.Id = pp.id
   where pp.PostTypeId in (1)
),

--- create histogram of counts
c as (
   select n_votes, pyear, count(n_votes) counts
   from b 
   group by n_votes, pyear
),

--- create histogram of percentage
d as (
   select n_votes, pyear, 100.0*counts/sum(counts) over(partition by pyear) percentage
   from c
)

--- display first values

select n_votes, cast(pyear as char) year, percentage 
from d
where n_votes < 11
order by pyear, n_votes

fork query

edited nov 21 19

created nov 21 19 anonymous