16

I'm curious about the social elements of Stack Exchange Sites and are looking for the Site that has the most isolated userbase. That is: The site with the most users that are not (active) on any other SE site.

While I could write a program that crawls through the user bases of the smaller sites (thus ignoring SO, SU, etc.) maybe there is a simpler way to get that statistical information.

Background: In the sites where I am a regular, I observe the same rules and tactics on how to handle which situation. While thats good and one of the reasons I like SE, I also think that it would be a good idea to look at sites where the user base comes to novel ideas about such things.

4
  • 1
    On a hunch, it's probably a site "imported" into Stack Exchange as-is with existing strong user base. Two candiates are mathoverflow.net or judaism.stackexchange.com. :) Commented Mar 18, 2015 at 9:48
  • It's a bit old now but this was posted a few years ago: meta.stackexchange.com/questions/157976/…
    – PeterJ
    Commented Mar 18, 2015 at 9:53
  • You could do that with accountid in the users table in sede across all databases but that requires a tricky db script and I doubt if yours will run within 2 minutes if implemented. Here is an example of a cross-database datacollection query in SEDE.
    – rene
    Commented Mar 18, 2015 at 9:54
  • @ShadowWizard Many Judaism.SE users are active on the rest of SE as well. Me, for example. Despite that, we indeed do a lot of things differently.
    – Scimonster
    Commented Mar 18, 2015 at 15:11

1 Answer 1

15

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
7
  • 5
    One issue here is that larger sites are full of one-post accounts that are not really users: they asked a question in 2011 and were never seen again. They don't really say anything about how isolated the site is now. If you restricted the query to somewhat established users (say 200 rep), the result may be quite different.
    – user259867
    Commented Mar 19, 2015 at 22:44
  • 1
    Agreed, the question asks "who are active on only one site", not "who have an account on only one site". Perhaps refine the query to check if the user has any recent activity in the past 60 days on any other site?
    – nhinkle
    Commented Mar 20, 2015 at 6:14
  • 1
    @Woodface that is now added (cc @nhinkle)
    – rene
    Commented Mar 20, 2015 at 19:49
  • Cool. I never heard of Tridion site, but that's to be expected.
    – user259867
    Commented Mar 20, 2015 at 19:55
  • 1
    @Woodface trust me, keep it that way...
    – rene
    Commented Mar 20, 2015 at 19:59
  • How do the results change if you include private beta sites like the edx ones? Great answer btw! Commented Apr 6, 2015 at 14:10
  • 1
    @AngeloFuchs I can only use what is in SEDE. I asked a follow up question to see if I could get them added but that went without luck
    – rene
    Commented Apr 6, 2015 at 14:24

You must log in to answer this question.

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