I am trying to find out if ALL the data available from the API and through SEDE queries is available if I took an offline data dump.

So for example,

  1. Could I get the user reputation as of a particular date (say Jan 1, 2017) from the data dump (and only the data dump)
  2. Could I get all user profile data thats publicly available from the api?
  3. Can I get reputation leagues from the data dump as well?
  • 1
    You got an answer about reputation data in your previous question; is it unclear or something? – fbueckert Aug 3 '18 at 17:24
  • This question specifically relates to whether the information is available from the data dump. i.e. is there a difference between data in the data dump and data in the api. For example, I would expect some profile info not to be in the dump because profile info may not be creative commons. – cnf Aug 3 '18 at 17:49

No, it is not.

In Database schema documentation for the public data dump and SEDE you'll find the answer with all the tables. The tables that are in italics can also be found in the Datadump. The rest is only available in SEDE.

In the API you'll find for some datatypes more data (for example reputation history).

For your specific sub questions:

  1. Could I get the user reputation as of a particular date (say Jan 1, 2017) from the data dump (and only the data dump)

You can get close to the same result as offered on Determining user's reputation as of particular date? but you have to replace Suggestededits (as that table is missing) with something like this

select min(ph.creationdate) creationdate
     , min(ph.postid) as [Post Link]
     , min(ph.userid)  as [User Link]
from posthistory ph
inner join votes v 
   on ph.postid = v.postid 
  and ph.creationdate between v.creationdate 
                          and dateadd(d, 1, v.creationdate)
where posthistorytypeid in (4,5,6,7,8,9) -- edit / rollback
and v.votetypeid = 16  -- suggestededit appected vote
group by revisionguid

This is inaccurate if more then one user edit a post on the same date but it is the best you can do within the Data Dump

Even with all those tables you still have an estimate, not 100% accurate reputation calculation.

  1. Could I get all user profile data that's publicly available from the API?

Let us match the users table vs User Type:

Users Table        User type
Reputation         reputation
CreationDate       creation_date
DisplayName        display_name
LastAccessDate     last_access_date
WebsiteUrl         website_url
Location           location
AboutMe            about_me
Views              view_count
UpVotes            up_vote_count
DownVotes          down_vote_count
ProfileImageUrl    profile_image
AccountId          account_id

This means you're missing the following fields that are in the User type in the API but are not in SEDE (or the datadump): accept_rate, age, answer_count, badge_counts, is_employee, last_modified_date,link, question_count, reputation_change_day,reputation_change_month, reputation_change_quarter, reputation_change_week, reputation_change_year, timed_penalty_date, user_id, user_type.

The field EmailHash is only present in SEDE but its use is deprecated and has no longer functional value. On older Datadumps (before May 2018) you'll also find the Age field in the Users table. The age field was removed due to GDPR

  1. Can I get reputation leagues from the data dump as well?

No, not directly, assuming you mean these. You could only get an estimation by creating and running your own query over posts and votes as found in the datadump.

You must log in to answer this question.

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