Is there any way to find which SE site is a gateway drug?

Question

Are there any pages/dashboards showing cross-site statistics indicating new enrollments, how many users become active on multiple sites, perhaps broken down to show the first website that new users joined?

For example, I first joined InfoSec SE and then got hooked to the QA format and joined several other SE sites.

Answer 1

I have created an query to answer the largest part of your question. The column labeled 1 will answer your question, that is the gateway drug site. At the end I explain a bit how this query works.

-- result table, don't rename and keep the site column
create table #results ( site nvarchar(250)
                       , uid int
                       , creationdate datetime
                       , aid int);

create index #idx_results_aid on #results (aid asc);

declare @sql nvarchar(max) = ''   -- holds build up sql string

-- build one biq union sql, for each db
select @sql = @sql 
+ iif( len(@sql) > 1 
     , 'union'
     , 'insert into #results'
) +
-- here goes the per site query, fully qualify the database objects
N'
select ''' + name + '''
     , id
     , creationdate
     , accountid
from ' + quotename(name) + '.dbo.users u 
where accountid is not null -- http://chat.meta.stackexchange.com/transcript/message/5222422#5222422
and accountid > 0 -- dont want community in there
'
from sys.databases
where database_id > 5
and (name not like '%.Meta' or name = 'StackExchange.Meta')
-- and not name = 'stackoverflow'

--print @sql

-- execute it
exec (@sql)

-- remove accounts that only participate on one site
delete from #results 
where aid in (
select aid 
from #results
group by aid
having count(*) = 1) 

-- show results
select site
     , [1]
     , [2]
     , [3]
     , [4]
     , [5]
     , [6]
     , [7]
     , [8]
     , [9]
     , [10]
from
(
 select site
   , rnk
   , count(*) as [usrcnt]
from (
select site
     , rank() over (partition by aid order by creationdate) as rnk
     -- rest of columns
     --, creationdate
     , aid
from #results
) as innerdata
group by site, rnk
) as outerdata
pivot (
  max(usrcnt)
  for rnk in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10])
  ) as pivotdata
order by 2 desc

drop table #results

When run today the result will look like this, although you might want to read the explanation first:

What you see in the table is per site, the number of users that have accounts on multiple sites. The 1 column holds the number of users with that site as their first site, the 2 column the number of users that have that site as their second site etc.

Query explanation

The query starts with filling a #results temporary table with all the users from all databases, taking their id, creationdate, and accountid. The accountid is used to link each site user. The #results table is populated by executing an massive SQL union statement.

The #results table then holds:

site          | userid | creationdate | aid
--------------------------------------------
Stackoverflow |    12  | 2009-01-01   | 4
ServerFault   |    18  | 2010-03-03   | 4
SuperUser     |    19  | 2011-05-05   | 4
Stackoverflow |    22  | 2010-02-01   | 5
ServerFault   |    26  | 2011-04-04   | 5
SuperUser     |    28  | 2012-07-10   | 5
Stackoverflow |    38  | 2009-12-12   | 8

This example shows 3 accounts. The next step deletes all rows that only have one site (so in above example, the row with aid = 8 will be removed).

For each account the rank for each site is determined based on the creationdate.

site          | userid | rank | creationdate | aid
--------------------------------------------
Stackoverflow |    12  |  1   | 2009-01-01   | 4
ServerFault   |    18  |  2   | 2010-03-03   | 4
SuperUser     |    19  |  3   | 2011-05-05   | 4
Stackoverflow |    22  |  1   | 2010-02-01   | 5
ServerFault   |    26  |  2   | 2011-04-04   | 5
SuperUser     |    28  |  3   | 2012-07-10   | 5

Once that is done we can count the number of rows per site and rank to project our final result.