I'm trying to join between stackoverflow.com's dataset Posts and Users files and I'm having trouble. I want to add the user profile of the question poster to a Post.

Do I use User.AccountId or User.Id? Do I use Posts.OwnerUserId? Neither seems to work. I get no hits. I'm using the 2019-08-05 dataset. The semantics are never explained anywhere. I think AccountId is the global user ID and the UserId is the per-site ID. But still I get no hits.


You would use Users.ID and join with the Posts.OwnerUserId.

The Users.AccountId references the Global Network Account and can be used to link Users across databases/sites.

Here is an example query on SEDE for the Stack Overflow database.

select title
     , owneruserid
     , tags
     , Posts.creationdate
     , Users.Displayname
     , Users.Reputation
from Posts
inner join Users on Users.Id = Posts.OwnerUserId
where Users.Id = 13969
and Posts.PostTypeId = 1

Here is what the result looks like:

enter image description here

This is kind of underdocumented in Database schema documentation for the public data dump and SEDE but the second answer from leerssej has an ERD where the relation is made clear:

enter image description here

I've highlighted the relation with a red free hand circle (that circle turned out rather flat)

| improve this answer | |
  • Thanks! This is very helpful. – user147894 Nov 13 '19 at 23:49

You must log in to answer this question.

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