I wanted to see the site's top sponsors - users that have paid bounties on questions that they didn't own.

I started off with a bounty-related existing query, selected the details into a subquery, and then grouped by sponsor and ended up with this query, which no longer has anything in common with the query I started off with:

select Sponsor,
count(*) Bounties,
sum(BountyOpen)/count(*) AvgBountyPaid,
sum(BountyOpen) BountyPaid,
sum(BountyClose) BountyAwarded,
avg(cast(DaysOpen as float)) AvgDaysOpen,
max(BountyCloseDate) LastBountyClosed
from (
select
bo.BountyAmount BountyOpen,
datediff(day, bo.CreationDate, bc.CreationDate) DaysOpen,
bc.BountyAmount BountyClose,
bc.CreationDate BountyCloseDate
from Posts q
inner join Votes bo on q.Id = bo.PostId
and q.PostTypeId = 1 -- Questions
and bo.VoteTypeId = 8 -- BountyOpen
left join Posts a on a.ParentId = q.Id
and a.PostTypeId = 2 -- Answers
left join Votes bc on a.Id = bc.PostId
and bc.VoteTypeId = 9 -- BountyClose
where q.ClosedDate is null
and bc.BountyAmount is not null
and op.Id != u.Id
) subquery
order by
sum(BountyClose) desc,
max(BountyCloseDate) desc


Is there anything else I could have done better?

-
Returns 10 rows in < 1 ms. –  Mat's Mug Jan 19 '14 at 22:59
10 rows returned in 1 ms (cached) :) –  David Harkness Jan 19 '14 at 23:26
@DavidHarkness good catch. It's more like 55 ms, with 41% x2 (82%) being spent doing a Clustered Index Scan on [Votes].[UIX_Votes_Id]. Does it get any better? –  Mat's Mug Jan 20 '14 at 1:00

I have looked at this query/report, and from the beginning I figured it must be missing something. I looked through the SQL, and can't identify it off-hand, so I figured I would build my own query, and see how they compare. The results I got are very different... :(

Edit: Found the problem

You cannot chain two outer joins.... Consider the query for the missing Quentin votes:

We know from the alternate query, and from Quentin's profile, that he offered a 150 bounty. This 150 does not show up on your query.

Here is a base query that matches your query and it should show this bounty.

it does not. But, if we convert the final outer-joins on alias a and alias bc to an equi-join, and make it a with statement, it all of a sudden works....

The reason is because we need the two outer joins, and the first one succeeds. The first one gets answers to the question (there may not be answers, so we need the outer join). The second outer-join looks for bounty-votes, and the bounty may not be awarded.

I am not sure why this is not working, could be a SQLServer bug?

Second update This issue also accounts for the missing 2 bounties from 200_success... because they are on questions which have answers, but the answers were not awarded the bounty.

This also explains why some offered-but-not-awarded bounties are working, because they were on questions with no answers.

Alternate query here

I have put together this report BountifulII. Obviously, there could be problems with my report too (offer it as a question? - Week-end challenge?).

Note that my report has a couple of odd --firewall fix comments in it, see this MSO Question for the reason... ;-)

My report does a more general process of calculating who offered bounties, and who was awarded bounties. It separates out awarded-bounties to those awarded to their own questions, and those awarded to any question). In the final report it lists the Promotions and PromotedAmount. These values should match with the values in your reports.... but they do not.

here is the revised SQL:

WITH Bounties AS (
SELECT
UserID AS UserID,
0 AS GetCount,
0 AS GetAmount,
COUNT(BountyAmount) AS GiveCount,
SUM(BountyAmount) AS GiveAmount,
SUM(case when Posts.OwnerUserId = Votes.UserId then 1 else 0 end) AS SelfCount,
SUM(case when Posts.OwnerUserId = Votes.UserId then BountyAmount else 0 end) AS SelfAmount
Posts
AND VoteTypeId = 8
GROUP --firewall fix
BY UserID
UNION
SELECT
Posts.OwnerUserID AS UserID,
COUNT(BountyAmount) AS GetCount,
SUM(BountyAmount) AS GetAmount,
0 AS GiveCount,
0 AS GiveAmount,
0 AS SelfCount,
0 AS SelfAmount
Posts
AND VoteTypeId = 9
GROUP --firewall fix
BY Posts.OwnerUserID
)

SUM(GiveCount - SelfCount) AS Pomotions,
SUM(GiveAmount - SelfAmount) AS PromotedAmount,
SUM(GetCount) AS GetCount,
SUM(GetAmount) AS GetAmount,
SUM(GiveCount) AS GiveCount,
SUM(GiveAmount) AS GiveAmount,
SUM(SelfCount) AS SelfCount,
SUM(SelfAmount) AS SelfAmount,
SUM(GiveCount + GetCount) AS EventCount,
SUM(GetAmount - GiveAmount) AS NetBenefit
FROM Bounties
GROUP -- firewall fix
BY UserID
ORDER BY PromotedAmount DESC, EventCount DESC, NetBenefit DESC

-
I'd +1 but I'll spare it for when you show me where I F'd up. :) –  Mat's Mug Jan 21 '14 at 1:27
+1 for finding a problem with my query anyway. lol. Upvote! –  Mat's Mug Jan 21 '14 at 1:33
I can't award you a bounty for this one right now, but... it tickles. Here, get the checkmark! –  Mat's Mug Jan 21 '14 at 2:44

You don't need the op join on Users since you can compare the user IDs in the question directly. Remove that join and in the where clause replace

and op.Id != u.Id


with

and q.OwnerUserId != bo.UserId


or move this to the first join on Votes.

-

I would change some things.

This:

inner join Votes bo on q.Id = bo.PostId
and q.PostTypeId = 1 -- Questions
and bo.VoteTypeId = 8 -- BountyOpen


kind of stuff needs to go in the where statement. The join on the ID's is ok because that is how the table relates, but the other two and situations are for filtering the results. That should be in the where statement.

where q.ClosedDate is null
and bc.BountyAmount is not null
and op.Id != u.Id
) subquery


is a typo. NOT EQUALS is <> in SQL

Inside the where statement you have

AND op.ID <> u.Id


that should be in a join, more specifically a FULL OUTER JOIN or maybe better a LEFT OUTER JOIN (LEFT JOIN) .

NOTE

The reason that I say this last one is because I have sort of done a little bit of research.

I have seen several questions where they had a <> in the where clause, and they were asking about performance.

Turns out that the way that SQL works a <> or a NOT IN clause in the where statement slows down the query, and it is better to try and OUTER JOIN or LEFT JOIN on that column where possible.

I mention this more in my answer.

In the answer I mention a Stack Overflow answer to this question.

This may or may not be usable in your query, but it is something nice to know.

-
!= isn't a typo. It works in TSQL and I prefer it over <> :) –  Mat's Mug Jan 19 '14 at 22:57
oh. well la de da. –  Malachi Jan 19 '14 at 23:20
If it makes any difference, MySQL simply moves everything in the joins to the where clause. –  David Harkness Jan 20 '14 at 1:07
For the record, not that it matters in this query… conditions in the WHERE clause are not exactly the same as conditions in the JOIN ON clause. You can use a JOIN ON condition to cause it to join NULL to NULL, for example; that's not possible in the WHERE clause. –  200_success Jan 20 '14 at 4:39
@200_success, I just looked at this comment stream again, you are right that the two conditions are different, that is what I was saying, OP has conditions that are visibly where conditions and not ON conditions. –  Malachi Jan 21 '14 at 16:53