7

I am quite new to creating queries in SEDE, but I am pretty sure one cannot distinguish bounties granted by an OP from third-party bounties. Am I right?

Could this be changed?

2 Answers 2

7

Now that user information is populated for bounty-related votes, you can compare Votes.UserId against Posts.OwnerUserId to determine if a bounty was offered by the OP, or someone else.

As an example, here are the last ten bounties on Stack Overflow (from the last time Data Explorer was updated), with an indication of whether or not they were self-offered.

4

I've made the following query that shows the count per type of bounty for users that offer bounties on their own questions.

The main query (a CTE reallt) called ownbounties joins the Votes (typeid=8), PostHistory (typeid=33), Posts, PostNotices and PostNoticeTypes tables to get the actual 'bounty" type (ClassId=2 in PostNoticeTypes).

The representation of that data is then handed off to a PIVOT query.

;with ownbounties as
(
select -- top 100 
       p.id 
     , p.owneruserid 
     , pnt.name
     , pnt.id [noteid]
     , v.creationdate [vcd]
     , pn.creationdate [pcd]
     , ph.creationdate [phcd]
from votes v
inner join posts p on p.id = v.postid
inner join postnotices pn on pn.postid = p.id
inner join postnoticetypes pnt on pn.postnoticetypeid = pnt.id
inner join posthistory ph 
      on   ph.postid = p.id 
       and ph.comment = pn.id -- comment has the FK for postnotice
where v.votetypeid = 8 
and v.userid = p.owneruserid -- bounty on own post
and pnt.classid = 2 -- bounties 
and ph.posthistorytypeid = 33 -- notice added
)

select owneruserid as [User Link]
     , [10] [outdated]
     , [11] [attention]
     , [12] [improve]
     , [13] [authorative] 
     , [14] [canonical]
     , [15] [reward]
     , [10]+[11]+[12]+[13]+[14]+[15] [total]
from (select owneruserid, id, noteid 
from ownbounties) as pvtdata
pivot 
(
  count(id) 
  for noteid in ([10], [11], [12], [13], [14], [15])
) as pvt
order by [10]+[11]+[12]+[13]+[14]+[15] desc

When run today this is what it shows:

query with bountie givers and 5 columns with counts

Keep in mind SEDE is updated once a week on Sunday.
Never forget that Monica Cellio created the awesome SEDE Tutorial.
Say "Hi" in SEDE chat.

2
  • Is this run on a single site, or network wide?
    – Luuklag
    Feb 6, 2021 at 19:11
  • @Luuklag on one site. It might be a tad too heavy to run on all sites.
    – rene
    Feb 6, 2021 at 19:49

You must log in to answer this question.

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