How to search for open questions with large open bounties?

Question

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?

Answer 1

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.