5

I'm trying to construct a query to find open questions with open bounties above a particular size.

Questions that either currently have or previously had bounties can be found on data.stackexchange.com by looking for associated Votes records with a VoteTypeId of 8 (= BountyStart).

The problem is when trying to restrict these to ensure the bounty hasn't already been awarded or closed.

Can think of two possible ways:

  1. Look for questions with no associated Vote record with a VoteTypeId of 9 (= BountyClose).
  2. Bounties last for a week so restrict the BountyOpen records to those with a CreationDate under a week ago: WHERE DATEADD(d, 7, vob.CreationDate) >= GETDATE()

But neither appears to be eliminating all questions where the bounty has ended. E.g. at the time of writing, only 2 of the 5 questions returned by this query (which includes both criteria) have an open bounty.

Am I missing something? Is there another way?

6

Data Explorer is not updated constantly. It's updated once a week on Sunday morning. A bounty lasts for 7 days, so a bounty started on a Monday after the data update wouldn't ever get shown by your query, because it would be expired by the time the data got updated again.

A better way to get this information would be using the Stack Exchange API. There's an API call for /questions/featured that returns a list in JSON format of all bountied questions. You could then parse that to sort the list by bounty size and display it.

  • Good answer but I'd like to do this all online if possible, ideally a one-click URL but if not, a bit of online copying and pasting would surfice. So am now wondering if there is any way of parsing/sorting the JSON results online... – Steve Chambers Aug 26 '13 at 17:38
  • @SteveChambers you could probably set up a jsFiddle. You can call the API and parse JSON from JavaScript. Although if you have your own site you could just set up the script there. – nhinkle Aug 26 '13 at 17:39
  • 1
    Thanks, sounds a good plan. Will look into doing this... – Steve Chambers Aug 26 '13 at 17:40

You must log in to answer this question.

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