How to query SEDE for user sign-up dates for a specific community

Question

We are looking to query a list of users to receive the sign-up dates to the Stack Overflow sub-community. Does anyone have a suggestion how to do this via SEDE?

Answer 1

You're looking for the creationdate in the Users table. Technically every user should have a single network account that joins all their user profiles across all sites. The creationdate of the network account is the real date you want but the network account table isn't public so we're left with looking at user creationdate.

So this query will give you the creationdate of 50,000 users on Stack Overflow. Switch to a different site with the site switcher under the query to get 50,000 users of another site.

Note that 50,000 rows is the maximum # of rows you can get from a query without tricks.

select id
     , concat('site://users/',id,'|', displayname) as [Link]
     , displayname
     , creationdate
from users
order by creationdate desc

We can take this a bit further to find for each network account the earliest creationdate of a per-site user profile. That needs a cross-database query to collect all users across the main sites and then U leverage a PIVOT query to get per AccoutId the first UserId. With those two values you can join back the collected users to select the earliest record for that account.

This is what that query looks like:

declare @sql nvarchar(max);

create table #users (
       id int
     , site sysname
     , displayname nvarchar(100)
     , creationdate datetime
     , accountid int
)
create clustered index #ix_users on #users(accountid, creationdate desc, id)

select @sql = concat('insert into #users'
, string_agg(concat('
select id
     , ''', name, '''
     , displayname
     , creationdate
     , accountid
     from', cast(quotename(name) as nvarchar(max)) , '.dbo.users
     where id > 0 and accountid > 0 
'),' union ')
)
from sys.databases
where database_id > 5
and (name not like '%.Meta' or name like 'StackExchange.Meta')

exec(@sql)


select u.*
from #users u
inner join (
  select Accountid
       , [1] [FirstUserId]
  from (
    select accountid 
         , row_number() over(partition by accountid order by creationdate) [row]
         , id
    from #users
  ) src 
  PIVOT
  (
     min(id)
     for row in ([1])
  ) pvt
) data on data.accountid = u.accountid and data.FirstUserId = u.id
-- given there are so many users
-- no matter how you order, StackOverflow accounts will dominate
-- better filter here on what you're looking for
order by u.creationdate desc 

The query result will look like this:

Keep in mind SEDE is updated each Sunday.
Do try the awesome tutorial written by Monica Cellio
Say "Hi!" in Chat