7

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 2

7

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:

enter image description here

3
  • This proves I have guessed :D
    – nicael
    Nov 6, 2015 at 19:20
  • But you're the true SQL magician - I thought the data.se queries only work for one site...
    – nicael
    Nov 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 ....
    – rene
    Nov 6, 2015 at 19:29
3

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.

5
  • And the SO question with the most was closed as off-topic ... Nov 6, 2015 at 18:42
  • @David Well, ok then :) Does it matter?
    – nicael
    Nov 6, 2015 at 18:45
  • Not really, it was just an observation ;) Bounties != Quality Nov 6, 2015 at 18:46
  • @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.
    – nicael
    Nov 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.
    – Mysticial
    Nov 6, 2015 at 19:02

You must log in to answer this question.

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