3

Using Data.SE, is there a way to tally up the total amount of users on a site?

I wanted to know how many users were on Anime.SE using the query tool.

  • "Registered" means confirmed email or just created an account? – nicael Jun 30 '15 at 17:01
3

I hope that I don't disappoint you but the number should be 967 as your query doesn't take into account the users that should already have been deleted as explained here.

I have adapated my reputation median query and created this query that really answers your question:

-- all databases
declare  db_c cursor for select [name] 
                         from sys.databases 
                         where database_id > 5 -- skip master, temp, model, msdb, Data.SE
                         and database_id <> db_id() -- skip current db as well

declare @db_c_name sysname   -- holds name of db after fetch
declare @sql nvarchar(max) -- holds build up sql string

-- result table
create table #all_accounts ( accountid int)

-- create a union of all accountid's across
-- all databases
set @sql = N'insert into #all_accounts '                            
open db_c
fetch next from db_c into @db_c_name
while(@@FETCH_STATUS = 0)
begin
    set @sql = @sql + N'select accountid from ' + QUOTENAME(@db_c_name) + N'.dbo.users'

   fetch next from db_c into @db_c_name
   IF (@@FETCH_STATUS = 0) 
   BEGIN   
     set @sql = @sql + N' 
     union 
     '
   END
end
close db_c
deallocate db_c

print @sql
exec(@sql) -- execute the insert with the unions


select count(*) as [number of users]
 from [users] u
 -- https://meta.stackexchange.com/questions/146450/delete-old-unloved-users/146452#146452
 where u.lastaccessdate > dateadd(m, -6, getdate()) -- active in last 6 months ago
 and u.reputation > 1 -- more then 1 rep 
 and exists (select 1 from posts where owneruserid = u.id) -- visible post
 and exists (select 1 from #all_accounts where accountid = u.accountid)  --other accounts in the network


drop table #all_accounts
  • 1
    Wow, nicely done. – user246550 Jun 30 '15 at 18:43
3

Wow, that was quite easy... I came up with a simple query to find the amount of users:

SELECT COUNT(Id) FROM Users

As far as Anime.SE goes, there are 8626 users


A more specific query could entail using the WHERE keyword in SQL. For instance, to get the amount of users that are of the age 14:

SELECT COUNT(Id) FROM Users WHERE Age=14

You must log in to answer this question.