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,
SponsorRep,
round(sum(cast(BountyClose as float))/(SponsorRep + sum(cast(BountyOpen as float)))*100,2) PctSponsorRep,
count(*) Bounties,
sum(BountyOpen)/count(*) AvgBountyPaid,
sum(BountyOpen) BountyPaid,
sum(BountyClose) BountyAwarded,
avg(cast(DaysOpen as float)) AvgDaysOpen,
max(BountyCloseDate) LastBountyClosed
from (
select
u.DisplayName Sponsor,
u.Reputation SponsorRep,
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
inner join Users op on q.OwnerUserId = op.Id
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
inner join Users u on bo.UserId = u.Id -- bounty owner
where q.ClosedDate is null
and bc.BountyAmount is not null
and op.Id != u.Id
) subquery
group by Sponsor, SponsorRep
order by
sum(BountyClose) desc,
max(BountyCloseDate) desc
Is there anything else I could have done better?