3

Is there any way I can extract, from the Stack Exchange data set, the communities that a user joined?

I want to use this data set to extract this feature for all users: https://archive.org/download/stackexchange

3

Yes, this multi database query will do that. It will need an accountid (found in the URL of your networkprofile) as input.

All users have an accountid. The accountid is the same across all sites for the same user. To find how many sites a user has joined you basically get their accountid (found in the users table) and then look up that accountid in all databases (or all users.xml if you use the datadump).

declare @sql nvarchar(max)

create table #result (site sysname
                    , hostname sysname
                    , displayname nvarchar(150)
                    , id integer
                    )

select @sql = N'insert into #result' + STRING_AGG(concat(N'
select ''', name ,N''' as site
     , ''',hostname ,N'''
     , u.displayname
     , u.id
from ', quotename(name), N'.dbo.users u
where accountid = ##accountid##
'),N' 
union all')
from (select convert(nvarchar(max),name) name
    -- , convert(nvarchar(max),sitename)  sitename
    -- , meta
    -- , domain
     ,  concat( 
        -- based on an idea from Glorfindel 
        -- https://meta.stackexchange.com/a/321181
        (case sitename
        WHEN 'Audio' THEN 'video'
        WHEN 'Beer' THEN 'alcohol'
        WHEN 'CogSci' THEN 'psychology'
        WHEN 'Garage' THEN 'mechanics'
        WHEN 'Health' THEN 'medicalsciences'
        WHEN 'Moderators' THEN 'communitybuilding'
        WHEN 'Photography' THEN 'photo'
        WHEN 'Programmers' THEN 'softwareengineering'
        WHEN 'Vegetarian' THEN 'vegetarianism'
        WHEN 'Writers' THEN 'writing'
        WHEN 'Br' THEN 'pt'
        WHEN 'Mathoverflow' THEN concat((meta+'.'), sitename)
        ELSE case when sitename = domain then null else sitename end
        end +'.')
        , (case 
           when sitename <> 'mathoverflow' then (meta+'.') 
           else null 
           end) 
        , (case 
           when sitename <> 'mathoverflow' then concat((domain + '.'), 'com') 
           else 'net' 
           end)
        ) hostname
from (
select name
, case parsename(name,1) 
  when 'Meta' then parsename(name,2)
  else parsename(name,1) 
  end [sitename]
, case parsename(name,1) 
  when 'Meta' then 'meta'
  else null
  end [meta]
  , coalesce(parsename(name,3), parsename(name,2)) [domain]
from sys.databases
where database_id > 5
-- (name not like '%.Meta' or name = 'StackExchange.Meta')
) dbs
) dbsall

exec (@sql)




select 'total communities' [Site]
     , cast( count(*) as nvarchar) [Username on site]
from #result
union all
select site
     , concat('https://'
       , hostname
       , '/users/'
       , id
       , '|'
       , displayname) as [User name on site]
from #result

When run today this will be the result for your account:

Four communities sepideh is in

Do know that the Stack API does have an endpoint /users/{ids}/associated that returns that list in one call (assuming you joined < 100 sites).

{
  "items": [
    {
      "last_access_date": 1565464882,
      "creation_date": 1539521560,
      "account_id": 14540960,
      "reputation": 1,
      "user_id": 10502700,
      "site_url": "https://stackoverflow.com",
      "site_name": "Stack Overflow"
    },
    {
      "last_access_date": 1565466797,
      "creation_date": 1565464878,
      "account_id": 14540960,
      "reputation": 1,
      "user_id": 587946,
      "site_url": "https://meta.stackexchange.com",
      "site_name": "Meta Stack Exchange"
    },
    {
      "last_access_date": 1562759679,
      "creation_date": 1555683739,
      "account_id": 14540960,
      "reputation": 1,
      "user_id": 245323,
      "site_url": "https://stats.stackexchange.com",
      "site_name": "Cross Validated"
    },
    {
      "last_access_date": 1565464882,
      "creation_date": 1545340204,
      "account_id": 14540960,
      "reputation": 1,
      "user_id": 58037,
      "site_url": "https://stackapps.com",
      "site_name": "Stack Apps"
    }
  ],
  "has_more": false,
  "quota_max": 10000,
  "quota_remaining": 9815
}

Keep in mind SEDE is updated on Sunday at 03:00 UTC. Do try the tutorial. Say "Hi!" in SEDE Chat.

  • thanks rene, but I want to use xml datasets in this address to extract this feature: archive.org/download/stackexchange which one of datasets in there has this feature? – sepideh Aug 10 at 20:03
  • @sepideh none of those has that info in one go. You would need to load all users.xml files. Maybe use the stackapi, I've included an example of that in my answer as well. – rene Aug 10 at 20:08

You must log in to answer this question.

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