15

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.

  • 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. :) – Shadow The Princess Wizard Mar 18 '15 at 9:48
  • It's a bit old now but this was posted a few years ago: meta.stackexchange.com/questions/157976/… – PeterJ Mar 18 '15 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 Mar 18 '15 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 Mar 18 '15 at 15:11
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
  • 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 Mar 19 '15 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 Mar 20 '15 at 6:14
  • 1
    @Woodface that is now added (cc @nhinkle) – rene Mar 20 '15 at 19:49
  • Cool. I never heard of Tridion site, but that's to be expected. – user259867 Mar 20 '15 at 19:55
  • 1
    @Woodface trust me, keep it that way... – rene Mar 20 '15 at 19:59
  • How do the results change if you include private beta sites like the edx ones? Great answer btw! – Angelo Fuchs Apr 6 '15 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 Apr 6 '15 at 14:24
7

http://edx-cs169-1x.stackexchange.com and http://cs50.stackexchange.com. Most users there are students of those univerisites and don't use rest of SE sites.

Also: http://craftcms.stackexchange.com and http://expressionengine.stackexchange.com. Seems that many users of those platform (especially the first) don't participate in other SE sites. (But I am kind of exception: I had particpated in craft cms Stack Exchange and have 1k reps there)

http://emacs.stackexchange.com has pretty specific users, but not that much as previous mentioned.

Similar for http://vi.stackexchange.com.

  • 1
    lol, users there got forks and mod got two forks and a diamond - awesome! – Shadow The Princess Wizard Mar 18 '15 at 10:02
  • 2
    @ShadowWizard Whats a "Fork"? (I mean: I can see the symbol, but what does it mean?) – Angelo Fuchs Mar 18 '15 at 10:06
  • 1
    Good question about fork - it was used once to mark SE employees in a special team called CHAOS team, but it was disbanded long long ago, so doubt it's related. – Shadow The Princess Wizard Mar 18 '15 at 10:16
  • 1
    @ShadowWizard Reading through their meta I assume it marks the teachers / employees of the course staff. More forks = more important I guess. – Angelo Fuchs Mar 18 '15 at 10:24
  • 3
    @ShadowWizard Ah, there it is: meta.edx-cs169-1x.stackexchange.com/questions/111/… – Angelo Fuchs Mar 18 '15 at 10:27
  • nice find @Angelo! – Shadow The Princess Wizard Mar 18 '15 at 10:29
  • 5
    What, universities are allowed to set up their private sites on SE? This could result in some problems later on. – ghosts_in_the_code Mar 18 '15 at 16:55
  • "students of those universites" is incorrect. edX courses are taken by whoever wants to take them. – user259867 Mar 18 '15 at 17:05
  • @Wood first two sites mentioned in my answer – nicael Mar 18 '15 at 17:07
  • Sites != universities. – user259867 Mar 18 '15 at 17:10
  • What makes you list Emacs and Vi and Vim? Nearly every user there also participates in some other Stack Exchange site. – 200_success Mar 18 '15 at 23:41
  • I think this answer is correct for the edx sites but if my query is correct I get a totally different outcome. – rene Mar 19 '15 at 22:02

You must log in to answer this question.

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