Out of all the questions on SE, which has had the most bounties? I don't care about total amount of rep, just how many separate bounties there were.
2 Answers
I dropped the query from Nicael in my Multi-db query template and this is the result:
-- start create url from dbname
IF OBJECT_ID ( '#siteurl', 'P' ) IS NOT NULL
DROP PROCEDURE #siteurl;
GO
create procedure #siteurl
@dbname nvarchar(250),
@res nvarchar(250) OUT
as
begin
-- we have three sites that have borked DNS entries
set @dbname = (case @dbname
when 'StackExchange.Audio' then 'StackExchange.Avp'
when 'StackExchange.Audio.Meta' then 'StackExchange.Avp.Meta'
when 'StackExchange.Mathoverflow.Meta' then 'net.Mathoverflow.meta'
else @dbname
end)
-- and one of those doesn't want to end with .com
if @dbname <> 'net.Mathoverflow.meta'
set @dbname = 'com.' + @dbname
exec #recursesiteurl @dbname, @res OUTPUT
end
GO
-- create url from dbname
IF OBJECT_ID ( '#recursesiteurl', 'P' ) IS NOT NULL
DROP PROCEDURE #recursesiteurl;
GO
create procedure #recursesiteurl
@dbname nvarchar(250),
@res nvarchar(250) OUT
as
begin
declare @pos integer
declare @rec nvarchar(250)
set @res = @dbname
set @pos = CHARINDEX('.', @dbname)
if (@pos > 0 )
begin
set @rec = substring(@dbname, @pos+1, len(@dbname))
exec #recursesiteurl @rec, @rec output
set @res = @rec
+ '.'
+ substring(@dbname, 0, @pos)
end
end;
GO
-- all databases
declare db_c cursor for select [name]
from sys.databases
where database_id > 5 -- skip master, temp, model, msdb, Data.SE
declare @db_c_name sysname -- holds name of db after fetch
declare @sql nvarchar(max) -- holds build up sql string
-- result table
create table #all_posts ( site nvarchar(250)
, bcount int
, id int
, title nvarchar(250)
);
open db_c
fetch next from db_c into @db_c_name
while(@@FETCH_STATUS = 0)
begin
set @sql = N'use '+ QUOTENAME(@db_c_name) +';
declare @url nvarchar(250)
exec #siteurl ''' + @db_c_name + ''', @url output
insert into #all_posts
select TOP 1 @url
, Count(Id) AS IdCount
, PostId
, (select title from posts p where Postid = p.id)
FROM Votes
WHERE BountyAmount IS NOT NULL
AND VoteTypeId = 8 -- BountyStart
GROUP BY PostId
ORDER BY Count(*) DESC;'
exec (@sql)
fetch next from db_c into @db_c_name
end;
close db_c;
deallocate db_c;
select replace(
replace(
replace(site,
'.StackExchange.com',''),
'.com',''),
'.net','') as [site]
, 'http://'
+ site
+ '/q/'
+ cast(id as nvarchar)
+ '|'
+ title as question
, bcount
from #all_posts
order by bcount desc
drop table #all_posts
drop procedure #recursesiteurl
drop procedure #siteurl
When you run this we can confirm the correctness of Nicael's answer:
-
-
But you're the true SQL magician - I thought the data.se queries only work for one site...– nicaelNov 6, 2015 at 19:23
-
@nicael it just happens to work but you either need the exec function and build up a sql string (when done wrong you get 150 errors) or a script that generates a single query with databasename.schema.table. Oh, and the timeout is still 120 seconds ....– reneNov 6, 2015 at 19:29
It's impossible to get those stats from all SE sites at once, but per this query, here on Meta the max amount of bounties is 17, and on Stack Overflow, it is 13.
I doubt any other site has larger numbers though o.0 on Tex it is 22.
On the other relatively big SE sites, the number varies from 3 to 7 with Physics topping at 11.
-
-
-
-
@David But the fact whether the question is closed or not doesn't always affect the question quality. At some point of time, it was on-topic, now it is not because of scope changing slightly as the time passes, did the quality of the question decrease? I don't think so.– nicaelNov 6, 2015 at 18:55
-
@DavidPostill The question with 13 bounties on SO was out of desperation from the OP who had a legitimate problem which became off-topic as the site evolved. The question with 10 bounties was repeated advertising from the top answerer before a mod put a stop to it. Nov 6, 2015 at 19:02