How can I look up any bounties (active and previous ones) I ever offered with any of my site accounts?

Question

Is there a way to look up the bounties I have ever offered with any of my individual site accounts? Preferably I'd like the active ones first, sorted from newest to oldest, and then the "expired" ones, sorted the same.

Answer 1

Here is the SEDE query that gives you all your offered bounties across the network. It needs your accountid (219454) as found in the url from your network profile.

declare @sql nvarchar(max)

create table #result (site sysname
                    , hostname sysname
                    , postid integer
                    , title nvarchar(250)
                    , bountyamount integer
                    , creationdate date
                    , userid integer)

select @sql = N'insert into #result' + STRING_AGG(concat(N'
select ''', name ,N''' as site
     , ''',hostname ,N'''
     , v.postid
     , q.title 
     , v.bountyamount
     , v.creationdate
     , v.userid
from ', quotename(name), N'.dbo.votes v
inner join ', quotename(name), N'.dbo.posts q on q.id = v.postid
inner join ', quotename(name), N'.dbo.users u on u.id = v.userid
where v.votetypeid = 8 -- bounty start
and u.accountid = ##accountid##
'),N' 
union all')
from (select convert(nvarchar(max),name) name
    -- , convert(nvarchar(max),sitename)  sitename
    -- , meta
    -- , domain
     ,  concat( 
        -- based on an idea from Glorfindel 
        -- https://meta.stackexchange.com/a/321181
        (case sitename
        WHEN 'Audio' THEN 'video'
        WHEN 'Beer' THEN 'alcohol'
        WHEN 'CogSci' THEN 'psychology'
        WHEN 'Garage' THEN 'mechanics'
        WHEN 'Health' THEN 'medicalsciences'
        WHEN 'Moderators' THEN 'communitybuilding'
        WHEN 'Photography' THEN 'photo'
        WHEN 'Programmers' THEN 'softwareengineering'
        WHEN 'Vegetarian' THEN 'vegetarianism'
        WHEN 'Writers' THEN 'writing'
        WHEN 'Br' THEN 'pt'
        WHEN 'Mathoverflow' THEN concat((meta+'.'), sitename)
        ELSE case when sitename = domain then null else sitename end
        end +'.')
        , (case 
           when sitename <> 'mathoverflow' then (meta+'.') 
           else null 
           end) 
        , (case 
           when sitename <> 'mathoverflow' then concat((domain + '.'), 'com') 
           else 'net' 
           end)
        ) hostname
from (
select name
, case parsename(name,1) 
  when 'Meta' then parsename(name,2)
  else parsename(name,1) 
  end [sitename]
, case parsename(name,1) 
  when 'Meta' then 'meta'
  else null
  end [meta]
  , coalesce(parsename(name,3), parsename(name,2)) [domain]
from sys.databases
where database_id > 5
and (name not like '%.Meta' or name = 'StackExchange.Meta')
) dbs
) dbsall

exec (@sql)



select site
     , concat(
       'https://'
       , hostname
       , '/q/'
       , postid
       , '|'
       , title) as [Question]
     , bountyamount
     , creationdate
from #result
order by creationdate desc

When run today this is your result:

SEDE is updated on Sunday, this might not have the latest offered bounties but I don't think that is big problem for bounties as they can only run for 7 days max.

Don't forget to give the awesome tutorial a try and do say "Hi!" in chat