Meta Stack Exchange is a question and answer site for meta-discussion of the Stack Exchange family of Q&A websites. It only takes a minute to sign up.
Sign up to join this community
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
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:
Keep in mind SEDE is updated once a week on Sunday.
