1

How can I find all the StackExchange websites that I'm part of as well as I'm not part of using the Data Explorer..?
In addition, if it's possible, I would like to use this query to do additional data manipulation such as to see avg answer score and fav tag on each website.

Related questions:
Allow cross-site queries in data explorer
Interesting queries on Data Explorer

  • Related. Also, I'm pretty sure such thing isn't and won't be possible to prevent trivial way to detect accounts of people who chose to Hide them. It's not perfect of course, but currently if one change their name on the hidden site and doesn't have top posts in there, it is really hard to find it. – Shadow Wizard Mar 5 at 8:00
  • Favorite tag isn't stored in SEDE. – rene Mar 5 at 8:57
  • @rene. I used that as an example. Ex: Tag based on upvotes or accepted answers etc. – DxTx Mar 5 at 9:09
  • I made a few changes to Rene's awesome query and got this and this. – DxTx May 15 at 7:30
4

The following multi-database query finds for a given accountid (that is the id of your networkprofile) the sites you participate on. In the second result set you find total sites joined and the total number of sites. I assume you can get from there how many sites you don't have an account on.

declare @sql nvarchar(max)

create table #result (site sysname
                    , displayname nvarchar(150)
                    , id integer
                    , upvotes integer
                    , downvotes integer
                    , q integer
                    , a integer)

select @sql = N'insert into #result' + STRING_AGG(concat(N'
select ''', convert(nvarchar(max),name) ,N''' as site
     , u.displayname
     , u.id
     , u.upvotes
     , u.downvotes
     , sum(case p.posttypeid when 1 then 1 else 0 end) Q
     , sum(case p.posttypeid when 2 then 1 else 0 end) A
from ', convert(nvarchar(max),quotename(name)), N'.dbo.users u
left outer join ', convert(nvarchar(max),quotename(name)), N'.dbo.posts p on
  p.owneruserid = u.id
where accountid = ##accountid##
group by u.displayname, u.id, u.upvotes, u.downvotes
'),N' 
union all')
from sys.databases
where database_id > 5
and (name not like '%.Meta' or name = 'StackExchange.Meta')

exec (@sql)

select *
from #result

select (select count(*)
from #result) [sites with account]
, (select count(*)
from sys.databases
where database_id > 5
and (name not like '%.Meta' or name = 'StackExchange.Meta')) [total sites]

When run today for a random account number this is what your result might look like:

jeff Atwood sites

Keep in mind SEDE is updated once a week on Sunday.

  • Thank you for responding. I checked this using my profile ID. I have registered in 120 StackExchange sites. But this only shows 11 sites...? Maybe I'm doing something wrong? – DxTx Mar 6 at 4:26
  • @DT that query needed an left outer join. You have only posted on 11 sites, now fixed. – rene Mar 6 at 7:49
  • Awesome. This is what I wanted. Also, using the previous query, now I can get the active site, too. – DxTx Mar 6 at 8:11

You must log in to answer this question.

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