4

https://data.stackexchange.com/stackoverflow/query/new

I couldn't find a way to query where we received how much reputation. In the achievements section, we can see how much rep we received from which post. Is there a way to query this?

Also, how does Stack Exchange store this in a database since there is no open table given as far as I could see?

enter image description here

  • Related if not a duplicate: meta.stackexchange.com/questions/144716/… – rene Aug 9 '15 at 8:45
  • @rene do you know if SO uses a similiar query for achievements tab or they store it differently for speed purposes? – Thellimist Aug 9 '15 at 9:51
  • I'm sure they have that info cached/de-normalized/replicated in production. In SEDE we only get the raw data. I don't think they run something similar as my query, not on SO, nor on any other site I assume. In other words: that data access path is optimized for SO and used network wide and I would be surprised if it turns out to be different – rene Aug 9 '15 at 10:06
1

To recreate that achievements list somewhat (because not all reputation events are available in SEDE) I created the following query:

declare @accountid int = ##accountid:int?5431417##

-- result table, don't rename and keep the site column
create table #results ( site nvarchar(250)
                       , postid int
                       , creationdate datetime
                       , title nvarchar(250)
                       , reputation int
                       );

declare @sql nvarchar(max) = ''   -- holds build up sql string

-- build one biq union sql, for each db
select @sql = @sql 
+ iif( len(@sql) > 1 
     , 'union'
     , 'insert into #results'
) +
-- here goes the per site query, fully qualify the database objects
N'
select ''' + name + '''
     , p.id
     , cast(v.creationdate as date)
     , q.title
     , sum( 
       case 
       when p.posttypeid = 1 then -- Q
          case 
          when v.votetypeid = 2 then 5  -- upmod
          when v.votetypeid = 3 then -2 -- downmod
          else 0
          end
       else  
         -- A
         case 
          when v.votetypeid = 1 then 15 -- accept
          when v.votetypeid = 2 then 10  -- upmod
          when v.votetypeid = 3 then -2 -- downmod
          else 0
          end
       end
     ) as rep
from ' + quotename(name) + '.dbo.posts p
inner join ' + quotename(name) + '.dbo.posts q on q.id = coalesce(p.parentid, p.id)
inner join ' + quotename(name) + '.dbo.users u on p.owneruserid = u.id
inner join ' + quotename(name) + '.dbo.votes v on v.postid = p.id
where p.posttypeid in (1,2) 
and u.accountid= ' + cast(@accountid as nvarchar) + '
group by  p.id, cast(v.creationdate as date), q.title
union 
-- accepts
select ''' + name + '''
     , p.id
     , cast(v.creationdate as date)
     , p.title
     , sum(
       2
     ) as rep
from ' + quotename(name) + '.dbo.posts p
inner join ' + quotename(name) + '.dbo.users u on p.owneruserid = u.id
inner join ' + quotename(name) + '.dbo.votes v on v.postid = p.acceptedanswerid
where p.posttypeid = 1 -- Q 
and p.acceptedanswerid is not null
and v.votetypeid = 1 -- Accept
and u.accountid= ' + cast(@accountid as nvarchar) + '
group by  p.id, cast(v.creationdate as date),  p.title
union
-- sugested edits
select ''' + name + '''
     , p.id
     , cast(s.approvaldate as date)
     , q.title
     , sum( 
       2
     ) as rep
from ' + quotename(name) + '.dbo.suggestededits s
inner join ' + quotename(name) + '.dbo.posts p on s.postid = p.id
inner join ' + quotename(name) + '.dbo.posts q on q.id = coalesce(p.parentid, p.id)
inner join ' + quotename(name) + '.dbo.users u on s.owneruserid = u.id
inner join ' + quotename(name) + '.dbo.votes v on v.postid = p.id
where p.posttypeid in (1,2) 
and s.approvaldate is not null
and u.reputation < 2000 -- this is a shortcut
and u.accountid= ' + cast(@accountid as nvarchar) + '
group by  p.id, cast(s.approvaldate as date), q.title

'
from sys.databases
where database_id > 5
-- and (name not like '%.Meta' or name = 'StackExchange.Meta')

--print @sql

-- execute it
exec (@sql)

-- show results
select site
      , creationdate

       -- URL builder from Lamak http://stackoverflow.com/a/38129701
      , 'http://' 
      + case PARSENAME(site,1)
        when 'audio' then 'avp'
        else PARSENAME(site,1)
        end 
      + coalesce('.' 
        + case PARSENAME(site,2)
          when 'audio' then 'avp'
          else PARSENAME(site,2)
          end , '')
      + IIF(patindex('%.MathOverflow%',site) > 0
      , ''
      , coalesce('.' + PARSENAME(site,3), '')
      )
      + IIF(patindex('%.MathOverflow%',site) > 0
      , '.net'
      , '.com'
      ) 
      + '/q/'
      + cast(postid as nvarchar) 
      + '|'
      + title
      -- rest of columns
      , reputation
from #results
order by creationdate desc 

drop table #results

You need to provide an accountid which can be found on the url of your networkprofile.

When run today the result looks like this:

achievement list with reputation changes per question across the network

Keep in mind that SEDE is only updated once a week, in the weekend.

You must log in to answer this question.

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