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