3

I'm trying to understand which bounty reasons get the most usage, but I can't find the bounty reason anywhere in the database schema.

Also, is there any way to do a keyword search on all the table names and fields in the schema?

  • 3
    Well, it's not available anywhere, so good chance it's lost for good. (It's not available in the revisions.) – ShaWiz Aug 28 '16 at 21:18
4

The bounty reason is now stored in the public dataschema. Let me first show you can get the id for the post notice from the PostHistory

select top 1000 v.postid as [Post Link]
     , v.creationdate as [vote date]
     , ph.creationdate as [post notice datetime]
     , ph.text
     , ph.comment
     , v.bountyamount
     --  v.userid as [User Link]
from votes v 
inner join posthistory ph on ph.postid = v.postid
where  v.votetypeid = 8 -- bounty start
and ph.posthistorytypeid = 33 -- post notice added
and v.creationdate = cast(ph.creationdate as date)
order by v.creationdate desc

The number in the comment matches with the primary key of the PostNotices table. Here is a query that gives you the number of times a certain bounty reason is chosen:

select name
     , count(*) [# used]
from postnotices pn
inner join postnoticetypes pt on pt.id = pn.postnoticetypeid
where classid = 2 -- Bounty
group by name
order by count(*) desc

When run today you'll get the following result:

result of counts per bounty reason

In the PostNotices table you'll also find the custom text entered by users when they set the bounty.

I leave joining PostHistory and PostNotice as an exercise for the reader.

For your second question either use ctrl+F in the browser or you can query the views in the information_schema, for example columns:

select table_name
     , column_name
     , data_type
from information_schema.columns
where table_name like '%' + ##keyword:string## + '%'
or column_name like '%' + ##keyword:string## + '%'

when you run that for keyword user you get this result:

result of colmuns query

But maybe you didn't find Database schema documentation for the public data dump and SEDE or the Database Diagram of Stack Exchange model? which might give an better idea of what is possible/available.

  • I thought the tables and fields listed in the right column of the query editor were all the available fields. It seems adding a textbox to search in there wouldn't be super complicated. – bigblind Aug 30 '16 at 21:09
  • 1
    @bigblind that is true, I forgot most browser have a Ctrl+F function nowadays. Added that to my answer. Maybe a userscript is possible as well to add a searchbox – rene Aug 30 '16 at 22:17

You must log in to answer this question.

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