7

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.

1
  • 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
    Commented Jul 29, 2019 at 20:03

1 Answer 1

13

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:

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

3
  • 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. Commented Jul 29, 2019 at 20:03
  • Love it! To get my total number, I can just use the row count; to get the total reputation offered, I can download the .csv and apply sum() but I wonder if the sum could be displayed on the SEDE page?
    – uhoh
    Commented Feb 22, 2023 at 21:14
  • 2
    @uhoh only by adding one "virtual'" row with an extra union. something like adding select 'total' site, 1 question, sum(bountyamount) bountyamount, getdate() creationdate from (the result query here again) union (the result query here again)
    – rene
    Commented Feb 22, 2023 at 21:25

You must log in to answer this question.

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