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.

  • @rene: Hmm, just tried that snippet (good catch, that page wasn't offered while I entered my question), but it doesn't seem to do anything (after entering my network account ID). I'm puzzled. Definitely looks like it could be a duplicate. I'm sure expired bounties would be something that could be parameterized. – 0xC0000022L Jul 29 '19 at 19:51
  • the stack snippet only returns current active bounties. If I run it with the networkid 141090 I do get a result, so the script kind of works. – rene Jul 29 '19 at 20:03
  • Oh, right. Yeah, that must have been the issue here. Thanks. – 0xC0000022L Jul 29 '19 at 20:04

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##
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 
        , (case 
           when sitename <> 'mathoverflow' then concat((domain + '.'), 'com') 
           else 'net' 
        ) 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(
       , hostname
       , '/q/'
       , postid
       , '|'
       , title) as [Question]
     , bountyamount
     , creationdate
from #result
order by creationdate desc

When run today this is your result:

bounties offered on questions networkwide for a given accountid

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

| improve this answer | |
  • Whoa, thank you very much. I literally had no idea about the powerful tools that are seemingly available. Will have to check them out. Thanks for the links in particular. – 0xC0000022L Jul 29 '19 at 20:03

You must log in to answer this question.

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