Bad Questions as Percentage of New Questions (Consensus version)

1

Please login or register to vote for this query.

(click on this box to dismiss)

Looks for posts with more than half the amount of downvotes as they have upvotes Ordered by upvotes

Programming Puzzles and Code Golf

Q&A for programming puzzle enthusiasts and code golfers

set nocount on 

declare @VoteStats table (PostId int, up int, down int) 

insert @VoteStats
select
    PostId, 
    up = sum(case when VoteTypeId = 2 then 1 else 0 end), 
    down = sum(case when VoteTypeId = 3 then 1 else 0 end)
from Votes
where VoteTypeId in (2,3)
group by PostId

set nocount off

;with pc as(
select year(creationdate) as PostYear,month(creationdate) as PostMonth,  count(p.id) as NewQuestions 
from posts p
join posttypes pt on p.posttypeid = pt.id
join PostTags ptg on ptg.PostId = p.Id
join Tags t on t.Id = ptg.TagId
where pt.name = 'Question'
and p.ClosedDate is null
GROUP BY year(creationdate),month(creationdate)),
badpc as (
select year(creationdate) as PostYear,month(creationdate) as PostMonth,  count(p.id) as BadQuestions 
from @VoteStats 
join Posts p on PostId = p.Id
join posttypes pt on p.posttypeid = pt.id
--note the assumption here
--"bad question" is defined as one where downvote total > 0.5 * upvote total
--or where question has more than 4 downvotes
where (down > (up * 0.5) or down > 4) and p.CommunityOwnedDate is null 
and p.ClosedDate is null and pt.name = 'Question'
GROUP BY year(creationdate),month(creationdate))
select pc.*, badpc.BadQuestions, ROUND(100*CAST(BadQuestions AS DECIMAL(18,2))/CAST(NewQuestions AS DECIMAL(18,2)),2)as PercentBad 
from pc
join badpc on pc.PostYear = badpc.PostYear
and pc.PostMonth = badpc.postMonth
order by 1,2

Enter Parameters

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