I'm either missing something obvious or the other answer is wrong.
If we leave out private beta sites (like the edx sites) and we define isolation as users whose account only exist on one site without taking out the big sites SOFU and meta this is the result generated by this query:
site |only here |total users |perc
---------------------------------------------------------------------
StackOverflow |3362011 |4042562 |83.17
StackOverflow.Ja |3921 |6787 |57.77
Sound |2972 |5332 |55.74
Salesforce |5266 |11158 |47.19
Judaism |2103 |4505 |46.68
Gaming |27648 |63219 |43.73
Ubuntu |104963 |252532 |41.56
Math |71458 |173138 |41.27
Sharepoint |9823 |24765 |39.66
If I remove StackOverflow, SuperUser, ServerFault and Meta from the sites the result becomes as follows:
site |only here |total users |perc
--------------------------------------------------------------------
StackOverflow.Ja |6060 |6787 |89.29
StackOverflow.Br |10542 |14294 |73.75
Salesforce |8169 |11158 |73.21
Sharepoint |16809 |24765 |67.87
Tridion |846 |1247 |67.84
Magento |9145 |14018 |65.24
Ubuntu |158318 |252532 |62.69
Sound |3186 |5332 |59.75
Gis |20571 |34456 |59.7
Expressionengine |2095 |3658 |57.27
In the comments it was mentioned that I shouldn't look at having an account but also at activity. The suggestion was to only include user with more than 200 reputation and a lastaccessdate with the last 60 days. Using those parameters the outcome is indeed different and even show the site that was predicted in nicael's answer.
site |only here |total users |perc
-------------------------------------------------------------------
Tridion |124 |1247 |9.94
Craftcms |73 |1361 |5.36
Expressionengine |151 |3658 |4.13
Joomla |69 |1777 |3.88
Emacs |107 |2776 |3.85
Salesforce |430 |11158 |3.85
Vi |48 |1363 |3.52
StackOverflow.Br |483 |14294 |3.38
Judaism |151 |4505 |3.35
Rpg |379 |12644 |3
How does the query work?
I use two queries. One for generating the sql that goes inside a common table expression and one tht does all the hard work by running that sql across all databases.
The users table holds a field acountid and that field is stable across databases. To answer the question it was needed to get the accountid from all users across all databases where the database it self is used a grouping key.
This query generates the massive union:
select 'select accountid, ''' +
replace([name],'StackExchange.','') +
''' , (select count(*) from [' +[name]+ '].dbo.users ) from [' +
[name]+ '].dbo.users where reputation > 200 and datediff(d, lastaccessdate, getdate()) < 60 union all '
from sys.databases
where database_id > 5 -- skip master, temp, model, msdb, Data.SE
and name not like '%.Meta%'
and name not in (
'StackExchange.Meta'
, 'Stackoverflow'
, 'ServerFault'
, 'Superuser')
After I gathered the accounts that exist per site I use the following query to calculate the number of user only on one site as a percentage of the total number of users on that site:
; with all_users as (
select accountid, 'StackApps' as site , (select count(*) from [StackApps].dbo.users ) as tot from [StackApps].dbo.users where reputation > 200 and datediff(d, lastaccessdate, getdate()) < 60 union all
/* skipped 131 rows*/
select accountid, 'StackOverflow.Ja' , (select count(*) from [StackOverflow.Ja].dbo.users ) from [StackOverflow.Ja].dbo.users where reputation > 200 and datediff(d, lastaccessdate, getdate()) < 60 -- union all
)
select site
, count(*) as [only here]
, min(tot) as [total users]
, round(
cast(count(*) as decimal (9,2))
/ cast((min(tot) ) as decimal(9,2))
* 100
, 2) as [perc]
from all_users a
inner join (
-- all accountid's with their count
select accountid
, count(*) as cnt
from all_users
group by accountid
having count(*) = 1 -- active on exactly ONE site
) one on one.accountid = a.accountid
group by site
order by 4 desc
