Bad Questions as Percentage of New Questions (Heroic Dissent version)

1

Please login or register to vote for this query.

(click on this box to dismiss)

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 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 0 downvotes
where (down > (up * 0.5) or down > 0) 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