5

I've been trying to figure out how to get the number of Questions WITHOUT a Bounty attached to them in Data Explorer. I feel this should be a reasonably simple problem to solve, but I can't quite get there.

I've tried something simple like:

SELECT count(*)
FROM Posts
INNER JOIN Votes on Posts.Id = Votes.PostId
WHERE PostTypeId = 1 -- Is a question
AND Votes.BountyAmount is NULL -- Has No Bounty
AND Posts.CreationDate > DateAdd(day, -30, getdate()) -- Past 30 days

But the problem is that it returns a result for every ANSWER.

I know there must be a very logical way to approach this with SQL, but I'm struggling to find it. Thanks for any assistance!

1 Answer 1

5

You better join the Votes table on votetypeid = 8 because that links to the question where the bounty was started. The following query lists questions that have had no bounties attached to them, ever. Do notice that for Stack Overflow this query times out, hence the top 1000:

select top 1000 
       q.id as [Post Link]
from posts q 
where q.posttypeid = 1 -- Q
and q.CreationDate > DateAdd(day, -30, getdate()) -- Past 30 days
and q.id not in (
   select p.id
   from posts p 
   inner join votes v on v.postid = p.id
   where v.votetypeid = 8 -- bounty started
)

Keep in mind that you'll never get more then 50,000 records returned.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .